A Fine Slice Of SQL Server
Jess Pomfret takes us through some SPN pains:
But instead of getting a quick answer to my question, I just got the following error:
WARNING: [15:19:49][Get-DbaDatabase] Error occurred while establishing connection to dscsvr1 | The target principal name is incorrect. Cannot generate SSPI context.
Just reading the article brought back some bad troubleshooting memories for me… But as usual, I’m impressed that dbatools has a cmdlet or two to help with that troubleshooting.
Comments closedAbhinav Garg and Premal Shah have an announcement:
We’re excited to announce the general availability of Secure Cluster Connectivity (also commonly known as No Public IP) on Azure Databricks. This release applies to Microsoft Azure Public Cloud and Azure Government regions, in both Standard and Premium pricing tiers. Hundreds of our global customers including large financial services, healthcare and retail organizations have already adopted the capability to enable secure and reliable deployments of the Azure Databricks unified data platform. It allows them to securely process company and customer data in private Azure Virtual Networks, thus satisfying a major requirement of their enterprise governance policies.
Read on fore more detail about how this works.
Comments closedA jump box is a virtual desktop on the client’s domain that you can connect to using Remote Desktop. You’d obviously have to ask somebody for access to one, and you’d have to set up your development environment from scratch. This may not be a big issue if you’re in SSMS all of the time, but when you need the Power BI Desktop, Excel or even Visual Studio, this setup can take some time (not to mention asking for local admin credentials on the jump box).
A fourth option is to run the executable with runas and /netonly, like:
runas /user:domain\username ssms.exe /netonly
Jamie Wick takes us through a lengthy process:
SQL Server has been supported on several Linux distributions for a couple of years now. For some people, the primary stumbling block to implementing SQL Server on Linux is the need to retain Active Directory (ie Windows-based) authentication for their database users and applications. Below we’ll go over how to join a Linux server (Ubuntu release 20.04) with SQL Server 2019 to an Active Directory domain, and then configure SQL Server to allow Windows-based logins.
There are quite a few steps here and I appreciate Jamie providing us an image-filled, step-by-step process.
Comments closedKenneth Fisher answers a question:
One of the most powerful permissions available in SQL Server is control. But what exactly is it?
It’s essentially one step shy of db_owner, but that one step’s a doozy.
Comments closedMaelle Salmon explains how OAuth 2 works and also how you can use it in R packages:
When writing an R package wrapping an API using OAuth 2.0 you’ll need the user to grant access to an “app”, which will allow to create an access token and a refresh token. The access token will then often be passed to the API in a header when making requests, whilst the refresh token would be posted in a query string when the access token needs to be renewed.
Your problem is: how do I imitate a third-party app? Thankfully for you, in most cases the complexity can be handled by the httr package. For other cases, or if you want to e.g. only use curl, you will have to get creative.
Read on for more detail.
Comments closedRandolph West takes a look at a patch:
Microsoft announced updates today for all supported versions of SQL Server, for a privilege escalation vulnerability that leverages Extended Events. For security reasons no further details have been provided, but you can expect more information in the near future, now that this update is public.
Be sure to grab the latest update for your version of SQL Server.
Comments closedJack Vamvas contrasts Kerberos and NTLM:
There is a message found in SQL Server Error Logs similar to
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.net:60000 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
The line I’m interested in reviewing is Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. It’s good to first understand the differences between Kerberos & NTLM – both supported by SQL Server during AD authentication
Read the whole thing. It’s easy to fall into the trap of “Windows authentication = Kerberos”—I do that myself far too often.
Comments closedFollowing this lab: Lab: Serverless Synapse – From Spark to SQL On Demand – Microsoft Tech Community
You may experience this message:
Failed to execute the query because content of directory cannot be listed)
This is due to an extra step required to enable the AAD to pass through the firewall on the storage.
Click through for the solution.
Comments closedAlways On Availability Groups can support up to nine availability replicas, and while we don’t use anywhere near that many replicas in each of our clusters, we do have 2 replicas per cluster (3 servers total), with the replicas being used as a readable secondary.
Since we use readable secondaries in our environments, the application needs to connect to both the primary and the secondary servers with the same login. The catch is, logins don’t automatically sync across replicas. If the logins don’t sync, the application won’t connect to a secondary, which results in login failures.
Read on for one way to solve the problem.
Comments closed