none
Credential Caching in SQL Server RRS feed

  • Question

  •  

    Hello everbody,

     

    I have a windows client application which connects to a SQL Server local database using SQL Authentication. I want to convert this SQL Authentication to Windows authentication. But I do not want  to add the windows User directly as a SQL Login in the SQL Server, instead I have my corporate LDAP group to which all the corporate users will belong to and I am adding this LDAP Group to my SQL Server Database to access the local database. My queries here are,

    1. If I go disconnected from my corporate network, will the credentials be cached?

    2. What if a user is removed from the LDAP group when the user is working in a disconnected mode, will SQL Server still authenticate that user?

    3. If the authentication is possible as in Question 2, what is the duration of this credential caching?

     

    Any inputs regarding this will be of great help to me.

     

    Thanks,

    Shashi.

    Tuesday, November 18, 2008 3:56 PM

Answers

  • If the user logs in into the disconnected laptop, then connects to local SQL and then connect the laptop to AD then the connection is already established.
    If the user logs in into disconnected laptop, then connects laptop to AD and then connects to local SQL the connection may succeed. There are too many variables that come into play to give a definitive answer, like screen savers requiring a new password, attempts to use a shared resource on the network etc etc.
    If the user connects the laptop to AD and then logs in her laptop, the AD credentials are used and the cached credentials are erased. Connection to local SQL (based on AD group membership) will fail.

    Note that in practice is impossible to block an user that has admin control over its own machine from connecting to a local SQL instance.
    Wednesday, November 19, 2008 7:31 AM
    Moderator

All replies

  • Cached credentials are valid only for the authority that cached them. Ie. your user logs into the disconnected laptop using a cached credentials then he can only authenticate with a SQL instance installed on the same laptop. It doesn't matter wether we're talking about SQL or any other product, the authentication is performed by the LSA (Local Security Authority) and LSA understands it's cached credentials.

    Even if the user manages to connect to a local instance using cached credentials, there are numerous features in SQL Server that require a real connectivity with the AD.

    AD disconnected scenarios like this are much bettrer addressed by using SQL authentication.
    Tuesday, November 18, 2008 5:18 PM
    Moderator
  • So what if the user was removed from the AD group when the user is working in a disconnected mode. Will the user still be authenticated after he connects to the network?

    Tuesday, November 18, 2008 6:26 PM
  • If the user logs in into the disconnected laptop, then connects to local SQL and then connect the laptop to AD then the connection is already established.
    If the user logs in into disconnected laptop, then connects laptop to AD and then connects to local SQL the connection may succeed. There are too many variables that come into play to give a definitive answer, like screen savers requiring a new password, attempts to use a shared resource on the network etc etc.
    If the user connects the laptop to AD and then logs in her laptop, the AD credentials are used and the cached credentials are erased. Connection to local SQL (based on AD group membership) will fail.

    Note that in practice is impossible to block an user that has admin control over its own machine from connecting to a local SQL instance.
    Wednesday, November 19, 2008 7:31 AM
    Moderator