Solved

Sql server 2005 login from a non-domain user to domain sql

Posted on 2009-07-01
14
685 Views
Last Modified: 2012-05-07
I have a frustrating problem and have scoured the web for solutions. All solutions i have done to no avail.

Here is the scenario:

I have SQL 2005 running on a 2003 R2 server on a domain.
I have several domain users using an Access 2007 front end to the server with no problem. The users have logins associated with 3 databases on the server using windows authentication.
The server is set to mixed mode authentication.
I have set up a non-domain login on the server and can set up file dsn's to all three databases that test successful on a non-domain user machine. They use the non-domain user i set on the server. These have the same dsn names as those on the domain machines.
When i try to run the same Access app on the non-domain machine i get:
"Connection failed:"
"SQLState: '28000'"
"SQL Server Error: 18452"
"[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection"
I then cancel all the errors to leave the Access 2007 app with no data.
If i try to open one of the tables i get the same error which i click OK on.
I then get the "SQL Server Login" window with "Use Trusted Connection" checked. I can uncheck that and enter the non-domain user username and password and the connection works.

Can anyone help with why the app wont connect first time please
0
Comment
Question by:JoseJalapeno
  • 7
  • 5
14 Comments
 
LVL 10

Expert Comment

by:therealmongoose
Comment Utility
You could create a different file dsn to logon using a trusted connection instead of domain login for these 3 users, using a generic domain login on the sql server - appreciate this doesn't answer they question of why you are getting the issue, but may give you a way round it. I suspect the file dsn is chceking the user credtials against the domain users list first before it checks against the sql users list - is there a property that you can change in the sql admin page for the non domain users?
0
 

Author Comment

by:JoseJalapeno
Comment Utility
Its not 3 users its three databases that each user has to connect to.
 
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
you need to set sql server login on sql server and use it
 
0
 

Author Comment

by:JoseJalapeno
Comment Utility
I have SQL 2005 running on a 2003 R2 server on a domain.
I have several domain users using an Access 2007 front end to the server with no problem. The users have logins associated with 3 databases on the server using windows authentication.
The server is set to mixed mode authentication.
I have set up a non-domain login on the server and can set up file dsn's to all three databases that test successful on a non-domain user machine. They use the non-domain user i set on the server. These have the same dsn names as those on the domain machines.
When i try to run the same Access app on the non-domain machine i get:
"Connection failed:"
"SQLState: '28000'"
"SQL Server Error: 18452"
"[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection"
I then cancel all the errors to leave the Access 2007 app with no data.
If i try to open one of the tables i get the same error which i click OK on.
I then get the "SQL Server Login" window with "Use Trusted Connection" checked. I can uncheck that and enter the non-domain user username and password and the connection works.
0
 

Author Comment

by:JoseJalapeno
Comment Utility
I do wish that peole would read the question properly before replying
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
<I have set up a non-domain login on the server >
In sql server  - or Windows login or sql server login
  (Non- domain login - in some cases can be login from another tructed domain - so "non-domain " can be non from your doamin)
so it is <SQL Server Login>  <=> "NON- domain login"in your case
Then you said :
 < I can uncheck that and enter the non-domain user username and password and the connection works.>
Yes, it is what you need to do

 "Use Trusted Connection"  - it is for windows  login
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:JoseJalapeno
Comment Utility
I am talking about a workstation not domain "non-domain" machine.
The sql login is the same as the workstaion windows login.
I can only get machines on the same domain to use trusted connection
Sorry, terminology differences. I am not an expert, i can't know the correct names.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
workstaion windows login is not the same as sql server login
workstaion windows login  is still windows login  - and sql  login is still sql login
BTW: Windows login even local - is in format "YourPCName\login "
in sql login - format just e.g." login"
 
please login on sql server and make sure that sql login is there and use it ...
0
 

Author Comment

by:JoseJalapeno
Comment Utility
The sql login is there and i'm using it.
I am trying to connect from a laptop which is a member of a workgroup.
dsn setup on the laptop works using the sql login.
I can open Access and create an odbc connection using the dsn already created and it works.
If i close and reopen Access i get the "Login failed for user '. The user is not associated with a trusted SQL Server connection" error. It is as if the Sql server is only looking at the domain based logins for auth and not bothering with the sql login. The error brings up the "SQL Server Login" window with "Use Trusted Connection" checked by default which is not how i set up the dsn in the first place.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
Ok, the sql login is used - sql server part of your question is resolved
2nd part:
DSN: can you please check if the DSN has set to sql server login ?
check how the sql server objects have been linked to your access application
maybe there is VBA code that triggers connection to sql server that is still using Windows authentication instead of Sqllogin..
---
Try to add to sqllogin your Local Windows login - > in format like sqllogin :  username (exactelly the same as wind login just
without  SERVERname part )- password (the same that  you use for windows login) - permissions
 
0
 

Author Comment

by:JoseJalapeno
Comment Utility
The DSN's are definately set for SQL login
In access i use the external source ODBC wizard during which you select a DSN to use. When i do that after access is open it works fine. When i open that project again that is when the error is thrown. It seems to be an access thing. There is nothing in my code to do with connections at all.
Sorry but i dont know how to add my sql login to the windows login. Please explain how for me.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
you do not need <  add your  sql login to the windows login.>
please reread mu last post:
add sql server login to sql server the same as you local wind login

for example if you login on your PC\server where you sql server is installed
as  "yourPC\JoseJalapeno" and password 'secret"

then create sql server login that named

 "JoseJalapeno" and password 'secret"   - it maybe can help

holw to do it?
in sql server 2005->ssms


see
To create a SQL Server login that uses SQL Server Authentication
http://msdn.microsoft.com/en-us/library/aa337562.aspx
0
 

Accepted Solution

by:
JoseJalapeno earned 0 total points
Comment Utility
Thanks but i think i have figured out why this happens.
I tried creating a simple test application in visual studio 2008 and it worked no problem with a sql login
It seems to be an access problem. With a sql auth login, access does not give the opportunity to save the password therefore it will always ask for the password.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now