Problem solve Get help with specific problems with your technologies, process and projects.

Troubleshoot failed SQL Server logins from Active Directory domains

SQL Server login failures could be blamed on unsynchronized clocks and poorly configured delegation between servers. Contributor Serdar Yegulalp offers tricks for logging on from an Active Directory domain.

A SQL Server login that uses Windows-based authentication and an Active Directory user account may be vulnerable to a number of issues not visible at first. When a SQL Server-connected client suddenly begins receiving errors, such as "Login failed for user '(null)'," there's usually some problem with how the login authenticated between the two machines.

Most of the time this is solved by giving the user an Active Directory account in the appropriate domain if he doesn't already have one. But there may be other reasons for the problem:

Unsynchronized clocks

Although rare, when this does happen it can cause a number of different system-wide issues, login failures being one of them. If a server has a clock that's running even slightly out of sync from its clients', authentication for the logon session will fail because it will no longer match the timestamp on the authentication token.

Every machine in your organization should synchronize its clocks consistently against the same source -- whether an external clock like the server or an internal one (preferably an internal one also synched against one that's authoritative). If a clock on a server consistently drifts by more than one minute a day, the server should be considered suspect.

Delegation isn't properly configured

One advantage of using AD authentication with SQL Server is that you can be authenticated across linked servers – but this must be done by configuring delegation between servers. You may run into problems if there is no Service Principal Name (SPN) set for the server, or if servers don't communicate via TCP/IP (since Kerberos can only validate through TCP/IP and not other network protocols).

For more info, check out the following resources:

  • SQL Server password policies and password credentials

  • Learning Guide: Managing your Active Directory

  • Access SQL Server securely using Windows domain accounts

  • Dig Deeper on SQL Server Security

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.