Re: ODBC login problem

Thanks Dan

I followed your advice and ran the queries which ran well until I got to:

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

which gave me the following error:
"Msg 15291, level 16, State 1, Procedure sp_change_users_login , line 108
Terminating this procedure. The Username 'XXXXX' is absent or invalid."

The Username in sp_change_users_login is the same 'MyLogin' that I set up in
CREATE LOGIN MyLogin?

Thanks


"Dan Guzman" wrote:

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

  • Re: dsn-less connection
    ... It worked pretty much as I was hoping in prompting the user for a SQL ... Server login and was able to remove the specific dsn that's in the odbc ... straight to the SQL Server login prompt? ... with the login prompt but the Use Trusted Connection is checked on. ...
    (microsoft.public.access.security)
  • Re: Permission question - another one
    ... If I add an Sql Login it does add the TRAVAC\ in front of the names, ... seems to be users that were setup to use SQL Server Authentication. ... RAPTOR is the Server that has SQL Server running on it. ... > " I could think I am taking permissions away from someone, ...
    (microsoft.public.sqlserver.programming)
  • Re: Renamed Windows login not found in SQL Server 2000
    ... It's almost like SQL tuck some knowledge away in an area ... of memory that only gets released on Windows stop. ... > I am running SQL Server 2000 SP2 with Windows ... > login gets corrupted) I am unable to add the new login to ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server on XP Home Network
    ... The sa account is the system administrator "God" account witihin SQL ... This account is a SQL-Server login. ... You might be able to use Enterprise Manager to go in and create a new SQL ... Select the "SQL Server Authentication" ...
    (microsoft.public.sqlserver.setup)
  • RE: Getting Challenged when using SQL connection on .aspx page
    ... What would happen if i removed integrated security=sspi from the connection ... Just want the login token to be passed, ... Have webpage with following connection in the ... I have account to the database on the SQL server. ...
    (microsoft.public.dotnet.framework.aspnet)