Re: ODBC login problem

In setting up the ODBC DSN configuration, I use my old SQL Server
Authentication login and password, which I get the following error message:

"Login failed for user 'XXXXX'. The user is not associated with a Trusted
SQL Server connection".

First, make sure the target SQL 2005 server is configured to allow both Windows and SQL authentication. The trusted connection message indicates that you are attempting Windows authentication rather than SQL authentication.

Also, logins are not restored when you restore a database so you'll need to create the SQL login on the target server if it doesn't already exists. You'll also need to execute sp_change_users_login to remap the existing database user to the SQL login on the target server. Example below. See the Books Online for details.

USE MyDatabase
GO

--create a new login
CREATE LOGIN MyLogin
WITH PASSWORD = 'asecrtpasw0rd',
DEFAULT_DATABASE = MyDatabase
GO

--remap existing database user to new or exisitng login
EXEC sp_change_users_login
@Action = 'Update_One',
@UserNamePattern = 'MyLogin',
@LoginName = 'MyLogin'


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Calculator" <Calculator@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:F3252C86-D583-4B2A-AFC8-854E96DCCBCA@xxxxxxxxxxxxxxxx
This is possibly related to my restoration of the MS SQL DB from MSSQL 2000
to MSSQL 2005:

I backed up a database from MSSQL 2000 to move to a new server running MSSQL
2005.

The DB appeared to restore without a problem (although not yet confirmed);
however, my ODBC login is now not accepted?

In setting up the ODBC DSN configuration, I use my old SQL Server
Authentication login and password, which I get the following error message:

"Login failed for user 'XXXXX'. The user is not associated with a Trusted
SQL Server connection".

My ODBC login was what I used on my MSSQL 2000 setup and I presume the
backup and restore would copy these Authentication securities across to the
MSSQL 2005 setup?

Please let me know what I have overlooked.

Thanks

.

Relevant Pages