A Fine Slice Of SQL Server
Earlier this week I received a question from a customer on how to get Power BI to work with data in ADLSg2 that is secured using ACLs. I didn’t know the answer, but I knew who would know, and I looped in Ben Sack from the dataflows team.Ben answered the customer’s questions and unblocked their efforts, and he said that I could turn them into a blog post. Thank you, Ben!
Read on for the answer.
Comments closedPedro Lopes announces an update to the Kerberos Configuration Manager for SQL Server:
The Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services (SSRS), and SQL Server Analysis Services (SSAS).
Read on for what it does and a link to download the tool.
Comments closedRandolph West hits one of my bugbears with respect to the Windows command shell:
Using accepted good practice, the password and script were escaped with double quotes. (note that
instance,passwordanddatabaseare the replacement values in question):
sqlcmd -S instance -U maintenanceUser -P "password" -Q "dbcc checkdb ('database') with DATA_PURITY, NO_INFOMSGS;"Unfortunately, one of the passwords started with a double quotation mark which led to the command failing for one specific Express Edition instance.
Read on to see the mess as well as a way to extricate yourself from the mess.
Comments closedJamie Wick helps us solve the classic Kerberos double-hop problem:
If the Report Server service doesn’t have permission to delegate to the SQL Server, it will try to connect anonymously (step 4 in the diagram above). Which results in this login error:
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: <Client IP Address>]
Historically report server and SQL server services, that needed the ability to delegate authentication to other servers, were configured to run using an Active Directory user account. Enabling delegation on these accounts was simply a matter of setting the Trust level on the Delegation tab of the account’s properties (with Active Directory Users & Computers).
But Jamie is here to show us a better way.
Comments closedJames Livingston takes us through an interesting solution to a common problem:
If you’ve ever performed some impactful maintenance on a SQL Server, you probably notified users. If you’re great at documentation and already know exactly who to contact, this script isn’t for you. If you don’t have a user email list, this script will create it for you!
I used to manage 500 SQL Server instances and there was daily maintenance\changes going on constantly. I wrote this PowerShell script to automatically create an email list for me. This PowerShell script gathers the login information from an instance of SQL Server and then pulls their email address from Active Directory.
Read on to see the script in action.
Comments closedSean Gallardy isn’t too concerned about self-signed certificates in SQL Server:
This happens at startup and is completely transparent to both end users and administrators alike, apparently not so much to your security team utilizing the latest and greatest security tools available (yes, that’s sarcasm). One day you may get a notice that “SQL Server is using a self-signed certificate (issue #1) and that the key length/algorithm isn’t what our internal security team allows (issue #2). You must fix asap or be out of compliance!” Oh no, the dreaded compliance threat.
Sean is…not impressed with these issues and explains why.
Comments closedNed Otter continues a series on SQL Server 2019 Always Encrypted with Secure Enclaves:
In the first post of this series, we explored the requirements for using Always Encrypted with secure enclaves, as well as some of the limitations.
For this post, we’ll be using Powershell to install and configure the HGS server (required for “attestation”) as well as executing the steps required to configure the SQL 2019 server to work with HGS.
Read on for a few disclaimers and a detailed setup article.
Comments closedI discovered recently that there was a change made in SQL Server 2017 to the way that symmetric key passphrases are hashed. There’s a KB article that notes the fix, but basically the passphrases used to be encrypted with SHA1. That’s cryptographically insecure, so the algorithm was updated to SHA2.
This is a problem, and can cause some issues. I’ll show the issue and then how to get around it.
There’s not much detail in the KB article about what happens afterward: if you need to keep the trace flag on forever or if it eventually migrates everything over to using SHA2 for hashes.
Comments closedJohn McCormack wins today’s edition of Cloud Bingo:
I was a approached by a data warehouse developer who wanted to be able to take advantage of the feature in Azure Synapse Analytics (Formerly Azure SQL Data Warehouse) called ‘Resource classes’. Resource classes allow users in certain roles to have access to extra compute resources for their queries. This can increase their max memory per query (although there is an overall trade-off on concurrency). Unfortunately, due to this being managed via roles, the person looking to change resource classes needs to be in the db_owner role so they can manage database role membership. As I didn’t want to add the developer to the db_owner role, a different solution was required.
John gives us a nice answer to this problem. Click through for the script.
Comments closedReitse Eskens troubleshoots a linked server error:
This morning started with a bit of a nuisance. I got a message from one of our customers that a night job failed with an error.
First thing i checked after logging in was if the database and table exist on the server. Because the error states that de table doesn’t exist on the database or that there are insufficient rights. The table exists and the user to connect to the database has the rights to do so.
That didn’t do the job, so read on to see what the cause and solution were.
Comments closed