elgatto123
asked on
How to authenticate to SQL 2005 with windows permission even when off the domain?
BACKGROUND
I have a laptop which is on my company's domain sometimes, and other times NOT on the domain (e.g if I am on the road).
The laptop has an off-the-shelf vendor application that I will call 'zzz'. When I launch zzz, it tries to open a local database using my windows credentials. That is, my id 'homsim' is a member of AD group 'domn\zzzusers'. 'domn\zzzusers' has dbo permissions on the local db, so I am alllowed to connect to the db instance and access the database.
Even though we're using Windows Authentication, I can connect to the local db even if I am working offline e.g. NOT on the domain. (Using something cached, perhaps?) By the way, my laptop does not have wireless, so I'm sure I'm offline.
The current version of 'zzz' has MSDE/SQL 2000 (8.0.760).
WHAT HAS CHANGED
Now I am upgrading my laptop with a new version of 'zzz'. That new version has SQL 2005 Express (9.0.4035). Now I can connect to the database if on the domain, but NOT if I am offline.
I get an error "Login failed for user ''. The user is not assocaited with a trusted SQL Server Connection". In the event viewer there is the following: "SSPI handshake failed with error code 0x80090311 while stablishing a connection with integrated security; the connection has been closed. [CLIENT: 127.0.0.1]"
MY QUESTION
How can I configure this app (or Windows permissions or SQL configuration) so that the Windows user can connect to the local database even if off the network? The requirement would be that the user must have authenticated previously while they WERE on the network. I realize I should call the vendor (and I have) but they don't have an answer.
I have a laptop which is on my company's domain sometimes, and other times NOT on the domain (e.g if I am on the road).
The laptop has an off-the-shelf vendor application that I will call 'zzz'. When I launch zzz, it tries to open a local database using my windows credentials. That is, my id 'homsim' is a member of AD group 'domn\zzzusers'. 'domn\zzzusers' has dbo permissions on the local db, so I am alllowed to connect to the db instance and access the database.
Even though we're using Windows Authentication, I can connect to the local db even if I am working offline e.g. NOT on the domain. (Using something cached, perhaps?) By the way, my laptop does not have wireless, so I'm sure I'm offline.
The current version of 'zzz' has MSDE/SQL 2000 (8.0.760).
WHAT HAS CHANGED
Now I am upgrading my laptop with a new version of 'zzz'. That new version has SQL 2005 Express (9.0.4035). Now I can connect to the database if on the domain, but NOT if I am offline.
I get an error "Login failed for user ''. The user is not assocaited with a trusted SQL Server Connection". In the event viewer there is the following: "SSPI handshake failed with error code 0x80090311 while stablishing a connection with integrated security; the connection has been closed. [CLIENT: 127.0.0.1]"
MY QUESTION
How can I configure this app (or Windows permissions or SQL configuration) so that the Windows user can connect to the local database even if off the network? The requirement would be that the user must have authenticated previously while they WERE on the network. I realize I should call the vendor (and I have) but they don't have an answer.
Can you provide the connection string?
if it uses a connection string and using windows authetication then I don't see that changing.
You need to access the database (management studio or other) and go to logins.
Add your local account e.g. laptopname\ong and then map it to the desired username in the zzz database
You need to access the database (management studio or other) and go to logins.
Add your local account e.g. laptopname\ong and then map it to the desired username in the zzz database
My guess is that your app is connecting to SQL with a SQL account when off-line and that the new instance does not have
My guess is that your app is connecting to SQL with a SQL account when off-line and the new new instance does not have mixed mode authentication turned on.
if it were a sql account, would he not be prompted for credentials when the application started up (unless these were hardcoded somewhere)
That said, I tend to agree with you. Local windows account or SQl account.
That said, I tend to agree with you. Local windows account or SQl account.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could add your local windows user account to SQL and set it to use that.
If you are not in contact with the domain controller then you are not on the domain and therefore not equal to domn\zzzusers