Solved

How to authenticate to SQL 2005 with windows permission even when off the domain?

Posted on 2010-09-08
7
394 Views
Last Modified: 2013-12-04
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.
0
Comment
Question by:elgatto123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 33632950
I'm surprised (amazed) you could connect when offline using your previous version.

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
0
 
LVL 4

Expert Comment

by:ong-hh
ID: 33632951
Can you provide the connection string?
0
 
LVL 29

Expert Comment

by:QPR
ID: 33632959
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
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 5

Expert Comment

by:dbidba
ID: 33633121
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  
0
 
LVL 5

Expert Comment

by:dbidba
ID: 33633136
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.
0
 
LVL 29

Expert Comment

by:QPR
ID: 33633150
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.
0
 

Accepted Solution

by:
elgatto123 earned 0 total points
ID: 33655248
Thanks to all for your help. Based on your feedback, I confirmed that the vendor software was NOT passing sql/standard account when offline. (I confirmed this by writing my own vbscript which connects to the database using SSPI in the connection string, and got the same results.)  Here is how the issue got resolved: I had to force sql to use named pipes by pre-pending the string  'np:' in front of the server name  servername=np:(local).

This was the vendor's recommendation and it works now -- I get Windows authentication even if I'm disconnected from the network. I'd be less than honest if I said I understood why.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help  needed 3 43
Email Notifications for SQL 2005 9 37
Need help in debugging a UDF results 7 54
Database Mail Profiles 1 22
SHARE your personal details only on a NEED to basis. Take CHARGE and SECURE your IDENTITY. How do I then PROTECT myself and stay in charge of my own Personal details (and) - MY own WAY...
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question