new login ID issue

I migrated a SQL 2005 db from our development server to our QA server. I created a new ID for the application to use on the QA SQL server. The problem I'm facing is, I can't connect using the ID I created or even the ID that was already in the db on the test server. I did a backup of the DB on the test server, did a restore on the QA server and the ID's will not connect. I keep getting 'login failed for username'

The username is under the security tab of SQL, and I have it pointing to the DB that it will be connected to, but no success.

Any ideas on what could be causing this to fail?

Sorry, I accidently posted my reply as a new post :-(

For the existing ID, the reason is does not work is probably due to the fact it is an "orphan". Userid information in SQL exists in 2 places, the system database master, and in the application database. So, when you restored the application database, the userid does not have a corresponding entry in the master system database, so it is an orphan. Go here to read more http://support.microsoft.com/kb/274188/

The issue with the newly-created account is more likely due to something you did wrong. Here are some things to check:

1. verify the default database for the userid is correct

2. check the "user mapping" for the userid - does it have at least the public role on the database?

It should have db_datareader also, and maybe db_datawriter, depending on its intended use

3. test your odbc connection, there is a test button to verify it is working

good luck.

|||

I've done all of that.

I even backed up the db and restored it to a fresh SQL server that had no Db's or ID's on it. And I still can't connect to it. I'm only able to connect to the database on the SQL server it was created on.

|||

This does seem strange. Lets ignore the old Login for now as we'd expect that not to be able to access the new database due to the orphaned user issue mentioned previously.

Could you confirm how you created the new login/user? Is the new login enabled (check status tab in SSMS) and double check the new password :-)

If all that looks fine, would you possibly be able to script out the CREATE LOGIN statement from SSMS and also the CREATE USER statement from SSMS and post them here. Might help troubleshoot.

|||

I got it to work, but only after I added the domain username by using SQL Auth and I dont want that. I want to use my windows id and password so its like

domain\username;password

when i do that I get [username] not associated with a trusted connection.

|||

How are you trying to connect to sql server? Via SSMS or through an application? If through an app, what is your connection string?

|||

On the second part of your explanation, it seems like you are trying to use Windows credentials (domain\login;password as part of the connection string. If my assumption is correct, then it would explain the “not associated with a trusted connection” error.

Windows authentication uses the Windows credentials used to establish the connection, if you need to switch to a different set of credentials (i.e. run as a different user), you will need to impersonate the Windows user first (i.e. using runas).

For the invalid ID issue, I am also surprised that the suggestion by z-on didn’t work. We will need more information in order to help.

· What is the error message that you are getting back

· Does it fail when connecting using sqlcmd as well?

· Does it fail on connections made on the same machine?

· Assuming the user Is the a Windows principal (please correct me if I am wrong)

· Is it a domain or a local machine Windows user

o If it I s a domain user, does the new system has access to the domain

o Does the service account used for SQL Server has proper permissions on the domain

· Does it have access to the system via Windows role membership (i.e. member of sysadmin group) or directly.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine