Press "Enter" to skip to content

Category: Security

Using Active Directory Authentication for SQL Server on Linux

Published 2021-02-01 by Kevin Feasel

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 closed

Using OAuth 2 in R Packages

Published 2021-01-25 by Kevin Feasel

Maelle 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 closed

Security Update for SQL Server

Published 2021-01-13 by Kevin Feasel

Randolph 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 closed

Kerberos vs NTLM

Published 2021-01-05 by Kevin Feasel

Jack 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 closed

External Table Not Accessible because Content of Directory Cannot be Listed

Published 2020-12-30 by Kevin Feasel

Liliam Leme troubleshoots an error when working with a serverless SQL pool in Azure Synapse Analytics:

Following 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 closed

Sync Logins between Availability Group Replicas

Published 2020-12-21 by Kevin Feasel

Taryn Pratt has a process:

Always 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

Working with SQL Server Certificates in Powershell

Published 2020-12-16 by Kevin Feasel

Peter Schott walks us through the process of certificate maintenance:

I recently had a need to add certificates to SQL Servers throughout an organization. There were quite a few servers to update and the certificates would need to be generated using a given format. This would include some descriptors for the names, IPv4 address, and ensuring that SQL Server would see the certificate when finished.

I realized this would need some sort of script so reached for PowerShell and the dbatools module. There’s a function in dbatools that supports setting the SQL Server Certificate and I knew that would be useful. But first, I had to generate the certificate itself. I read up on this in PowerShell and there’s no “easy” button for creating a certificate at this time, especially not when you need to add extra properties.  Posts such as this one helped me get started. It works by creating an INF file, then shelling out to “certreq.exe” to generate the CSR file needed to obtain a certificate from a certificate authority. We had need to use the DNS name, the FQDN, and the IPv4 address as part of our certificate request, so I had to adjust my code to handle that.

Click through to see how.

Comments closed

Setting a Default Database for SQL Server Logins

Published 2020-12-01 by Kevin Feasel

Adrian Buckman shows us an issue with using a database other than master for a SQL Server login’s default:

This is one of them little options that I see which quite often gets little consideration or gets set to a user database without consideration of what the consequences may be if that DB becomes unavailable. There are going to be situations where setting a default other than master is essential and there are going to be situations where leaving as master suits best and this comes down to the individual requirements of each login, Recently I had to fix an issue with user connectivity for a single login, the user was getting failed connections when trying to connect to the SQL server when trying to access one of their legacy databases , everything appeared fine – User account was enabled the password hadn’t been changed and was therefore correct, the database they were trying to access was up and accessible but the SQL error log highlighted the real issue.

Click through for more details.

Comments closed

Displaying Always Encrypted Data in SSRS with a gMSA

Published 2020-11-18 by Kevin Feasel

Ryan Adams shows how we can view Always Encrypted data in SQL Server Reporting Services when SSRS uses a Group Managed Service Account:

Always Encrypted protects our data both at rest and in transit. To accomplish that, SQL only stores the encrypted data and cannot decrypt it; all the work is done by the client. In our case the client is SSRS and it is the account running the SSRS service that will need the certificate to decrypt data. Note that it is not the account running the report.

Click through for the solution.

Comments closed