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!
SQL Server 2008
(1)
SQL Server
(1)
Office 2007
(1)
Windows 7
(1)
SQLState
(1)
Database
(1)
Anotherclient
(1)
Unclick
(1)
  Mary Chipman [MSFT] replied to Rondy
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
  Rondy replied to Mary Chipman [MSFT]
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.
  Mary Chipman [MSFT] replied to Rondy
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
Create New Account
help
SQL Server 2008 R2 SQL Server Is this a full or cutdown version of SQL Server 2008 Enterprise? SQL Server Discussions SQL Server 2008 R2 (1) SQL Server 2005 (1) Windows Server
SSIS package in sql server 2008 SQL Server I am using sql server 2000 and want to move to sql server 2008 How to create / edit a ssis(dts) package in sql server 2008? Previously
linked servers between different sql versions?? SQL Server Can a SQL Server 2008 server have a linked server that is SQL Server 2008 R2?? We presently use a SQL Server 2008 for reporting
SQL 2005 Studio connect to SQL 2008 SQL Server How can I make my SQL Server 2005 Management Studio to connect to a remote SQL Server 2008? SQL Server Tools Discussions SQL Server 2000 (1) SQL Server 2008 (1) SQL
Clustering on SQL Server 2008 SQL Server Is failover clustering the only type of clustering available on SQL Server 2008? More specifically, does SQL Server 2008 support Active / Active clustering that is NOT failover clustering