ODBC login problem

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> 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@.discussions.microsoft.com> wrote in message
news:F3252C86-D583-4B2A-AFC8-854E96DCCBCA@.microsoft.com...
> 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|||Is the new server setup for both Windows and SQL
Authentication? Did you add the login to the SQL Server 2005
server?
You can also check for users not mapped to a login as well
as map users and SQL logins using sp_change_users_login. To
list the users, execute the following in the database:
EXEC sp_change_users_login 'Report'
See books online for more information on
sp_change_users_login
-Sue
On Mon, 11 Jun 2007 01:39:02 -0700, Calculator
<Calculator@.discussions.microsoft.com> wrote:

>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 MSSQ
L
>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|||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:

> 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 t
o
> 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@.discussions.microsoft.com> wrote in message
> news:F3252C86-D583-4B2A-AFC8-854E96DCCBCA@.microsoft.com...
>|||Thanks Sue
1) Your Question: > Is the new server setup for both Windows and SQL
> Authentication? Did you add the login to the SQL Server 2005
> server?
I am using the built-in account on the local system as shown in the "SQL
Server properties" in the SQL Configuration Manager.
From there I perform the following:
under "Administrative Tools" I select the "ODBC Data Source Administrator",
where I enter data in the "Microsoft SQL Server DSN Configuation".
Here I select the second option button: "With SQL Server Authentication
using a login ID and password entered by the user."
I also tick "Connect to SQL Server ..." and enter my Login ID and my Passwor
d.
My new Login ID and Password do not work with the newly created login and
Password using:
CREATE LOGIN MyLogin
WITH PASSWORD = 'asecrtpasw0rd',
DEFAULT_DATABASE = MyDatabase
GO
The same error results:
"Login failed for user 'XXXXX'. The user is not associated with a Trusted
SQL Server connection".
2) The results from your procedure: EXEC sp_change_users_login 'Report'
gave me 2 x UserName and 2 x UserSID
The UserSIDs I have never seen or used before: Should they be familar?
Thanks
"Sue Hoegemeier" wrote:

> Is the new server setup for both Windows and SQL
> Authentication? Did you add the login to the SQL Server 2005
> server?
> You can also check for users not mapped to a login as well
> as map users and SQL logins using sp_change_users_login. To
> list the users, execute the following in the database:
> EXEC sp_change_users_login 'Report'
> See books online for more information on
> sp_change_users_login
> -Sue
> On Mon, 11 Jun 2007 01:39:02 -0700, Calculator
> <Calculator@.discussions.microsoft.com> wrote:
>
>|||> 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."
I would expect this error if the 'MyLogin' user does not exist in the target
database. My understanding from your initial post is that the user already
exists so only steps needed were to add the login and remap.
If the user does not exist in the database, simply run:
CREATE USER MyLogin;
This will create new user 'MyLogin' and map it to the existing login
'MyLogin' you previously created.
Hope this helps.
Dan Guzman
SQL Server MVP
"Calculator" <Calculator@.discussions.microsoft.com> wrote in message
news:C20F8BC2-18A8-4F83-9BAD-527100D5F585@.microsoft.com...[vbcol=seagreen]
> 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:
>|||As Sue and I mentioned earlier, make sure you SQL Server is configured to
allow both SQL Server and Windows authentication. You can do this from SQL
Server Management Studio. Right-click on the server in object explorer,
select Properties-->Security and then select 'SQL Server and Windows
Authentication mode.
Hope this helps.
Dan Guzman
SQL Server MVP
"Calculator" <Calculator@.discussions.microsoft.com> wrote in message
news:F1F02198-D32F-481F-9E23-4F17BA62AECC@.microsoft.com...[vbcol=seagreen]
> Thanks Sue
> 1) Your Question: > Is the new server setup for both Windows and SQL
> I am using the built-in account on the local system as shown in the "SQL
> Server properties" in the SQL Configuration Manager.
> From there I perform the following:
> under "Administrative Tools" I select the "ODBC Data Source
> Administrator",
> where I enter data in the "Microsoft SQL Server DSN Configuation".
> Here I select the second option button: "With SQL Server Authentication
> using a login ID and password entered by the user."
> I also tick "Connect to SQL Server ..." and enter my Login ID and my
> Password.
> My new Login ID and Password do not work with the newly created login and
> Password using:
> CREATE LOGIN MyLogin
> WITH PASSWORD = 'asecrtpasw0rd',
> DEFAULT_DATABASE = MyDatabase
> GO
> The same error results:
> "Login failed for user 'XXXXX'. The user is not associated with a Trusted
> SQL Server connection".
> 2) The results from your procedure: EXEC sp_change_users_login 'Report'
> gave me 2 x UserName and 2 x UserSID
> The UserSIDs I have never seen or used before: Should they be familar?
> Thanks
> "Sue Hoegemeier" wrote:
>