It means that the username and password you specified in the
connection is valid. If you are using Windows authentication and SQL
Server server running with any of two mode “Mixed Mode” or
“Windows-only” authentication, SQL Server will request Local Security
Authority Subsystem Service (LSASS) to verify the credentials of the
specified user. Again if the windows account is a local machine account,
it is verified against local system security database and if it is a
domain account, LSASS then requests Active Directory to authenticate the
user.
Once this user is authenticated, a token is then passed to SQL Server that authentication is successfull. There are again two things in this authentication, they are NTLM or KERBEROS. KERBEROS needs SQL Server Service Principal Name to be registered in Active Directory agains the SQL Server node name or the SQL Server Virtual Server Name. If SPN’s are not registered, then connection to failback to NTLM depending on your environment settings. For more info about NTLM & Kerberos, refer this article
If the login is a SQL Server login, then SQL Server takes care of authenticating the user because SQL Server stores the username and password. If target SQL Server instance is running in Windows-Only authentication mode then though you are specifying correct password for the SQL login, the connection will fail because SQL Server will not allow Non Windows login. You can change this to make SQL Server to allow Mixed Mode Authentication (Both SQL logins and Windows logins) from SSMS -> Instance -> Properties -> Security -> Server Authentication. Please note that you need to restart SQL Server if you make this change.
Authorization:
Once authentication succeeds, authorization phase starts whereby SQL Server checks the permission for accessing the target database based on the TokenPerm cache built and if yes, the login succeeds and if not the connection will fail with State 16.
Once authorization completes, you can submit your queries to execute in the SQL Server instance.
Once this user is authenticated, a token is then passed to SQL Server that authentication is successfull. There are again two things in this authentication, they are NTLM or KERBEROS. KERBEROS needs SQL Server Service Principal Name to be registered in Active Directory agains the SQL Server node name or the SQL Server Virtual Server Name. If SPN’s are not registered, then connection to failback to NTLM depending on your environment settings. For more info about NTLM & Kerberos, refer this article
If the login is a SQL Server login, then SQL Server takes care of authenticating the user because SQL Server stores the username and password. If target SQL Server instance is running in Windows-Only authentication mode then though you are specifying correct password for the SQL login, the connection will fail because SQL Server will not allow Non Windows login. You can change this to make SQL Server to allow Mixed Mode Authentication (Both SQL logins and Windows logins) from SSMS -> Instance -> Properties -> Security -> Server Authentication. Please note that you need to restart SQL Server if you make this change.
Authorization:
Once authentication succeeds, authorization phase starts whereby SQL Server checks the permission for accessing the target database based on the TokenPerm cache built and if yes, the login succeeds and if not the connection will fail with State 16.
Once authorization completes, you can submit your queries to execute in the SQL Server instance.
No comments:
Post a Comment