SQL Server - SQL Server Authentication, NOT Windows NT

Asked By Rondy

18-Nov-09 02:27 AM

Hi all,

Running Windows 7 / SQL Server 2008 / Office 2007 (12) -- I set up ODBC with
SQL Authentication, specifiying a user name and password that connects just
fine when setting up ODBC and testing connection... but when I try to run the
Access database front-end, I get this message:

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'MYDOMAIN\MYWINDOWSLOGIN'.

But I was using SQL Authentication... whatever, right?  Click OK on message,
SQL Server Login window opens, unclick Trusted Connection, input SQL
Authentication Login ID and Password, click OK... exact same message comes up
like I am trying to connect using my Windows credentials.

How can I make it take the SQL Server creds?  My SQL server is already set
to mixed authentication, FYI.

Any help would be greatly appreciated, thanks!

18-Nov-09 12:12 PM

Can you connect with the same security credentials using another
client, such as SSMS? Are you using a DSN-less connection string?

--Mary

18-Nov-09 02:38 PM

I used SSMS to create the logins and assign database access, then the typical
Windows wizard for creating ODBC link on user pc.

The problem is that even though I set up the ODBC links to use SQL Server
Authentication because SQL Server will not accept our Windows logins (reads them
during setup but then will not create them), it seems like it is still trying to
use Windows Authentication since it is denying login for
MYDOMAIN\MyWindowsLogin instead of using MySQLlogin.

19-Nov-09 11:02 AM

Try logging on to SSMS using those security credentials instead of the
high-privilege account you are using to create them. If the problem
turns out to be on the user's PC with the ODBC DSNs you have created
(this is what I assume you mean by Windows wizard), then you might
want to consider passing your SQL authentication credentials using a
DSN-less connection. DSNs are difficult to manage, and because they
are located on client computers, represent a potential security
vulnerability because anyone who has access to the computer can obtain
the login and password.

--Mary