SQL On Linux: Common Active Directory Login Issues

Dylan Gray and Tejas Shah continue their troubleshooting series for SQL Server on Linux integrations with Active Directory:

1. When a user performs an AD connection, internally the user connects to a service principal name (SPN). The SPNs are in the form “MSSQLSvc/host.contoso.com:1433”, and they must be registered when setting up AD logins for SQL Server on Linux. When a client app requests a connection (e.g. sqlcmd), it takes the server users wish to connect to, prepends “MSSQLSvc/” and appends “:**<port>**”, and this is the SPN which the connection attempts to authenticate with.

So, if user connects with “sqlcmd -E -S host.contoso.com”, it authenticates with the SPN “MSSQLSvc/host.contoso.com:1433”, and everything succeeds. If user connects with “sqlcmd -E -S host”, it authenticates with “MSSQLSvc/host:1433”. If the SPN the client is authenticating with does not exist, the connection will fail. So, if the SPNs in the mssql.keytab are only “MSSQLSvc/host.contoso.com:1433”, users can only connect to “host.contoso.com”, not “host” and not to the IP. If user needs to be able to connect with variations of host name and IP address, then all appropriate SPNs should be created and configured in the mssql.keytab file.

Read on for more common issues and their solutions.

Related Posts

Preventing Credential Compromise When Using AWS

Will Bengtston walks us through techniques Netflix uses to protect credentials in AWS: Scope In this post, we’ll discuss how to prevent or mitigate compromise of credentials due to certain classes of vulnerabilities such as Server Side Request Forgery (SSRF) and XML External Entity (XXE) injection. If an attacker has remote code execution (RCE) or […]

Read More

Cross-Availability Group Login Management

David Fowler walks us through a problem about orphaned users and Availability Groups: Now, I’m pretty sure that most of us will have been in the position where, after a fail-over we get inundated with calls, emails, Skype messages and carrier pigeon drops letting us know that so and so can no longer access the […]

Read More

Categories

September 2018
MTWTFSS
« Aug Oct »
 12
3456789
10111213141516
17181920212223
24252627282930