April 1, 2008 at 1:41 pm
Not sure where to check this one:
1 The Trusted Connection box is not selected in the System DSN definition.
I believe I'm in mixed mode because I log on using a username and password through the ODBC versus letting the network authentication work.
April 1, 2008 at 3:30 pm
Was out for lunch...
Ok..
Did you created ODBC DSN manually or using some connection string to connect to database. If you are creating manually then try creating Sql Native Client DSN connection to database. You can do this from
ODBC Data Source Administration dialog..
Also follow the below steps..
Go to query window and execute this script on your user database to which your are trying to connect
through frontend.....
Execute AS Login = 'LIMSUSA'
Select SUSER_NAME() --- You should see LIMUSA
select -- change column name and table names from your database.
If you see result output then problem can be isolated to your connection string..
April 2, 2008 at 9:52 am
Msg 15406, Level 16, State 1, Line 1
Cannot execute as the server principal because the principal "LIMSUSA" does not exist, this type of principal cannot be impersonated, or you do not have permission.
get that error when running the script you put up within the database I am trying to get into.
As far as the DNS configuration I don't see anywhere through the tabs where you even have a choice for a trusted connections to check or uncheck.
April 2, 2008 at 10:38 am
OK, check under the SQL Server, Security, Logins folder in Management Studio. Is this user listed? If not, that could be the problem. I'd expect to see the domain listed as well.
If you are passing the name and pwd through ODBC, then you are using SQL Authentication, not Windows. You should make sure that the Server Properties (Security section) has Mixed mode enabled.
You should be able to make a connection using SQLCMD or an SSMS query window and specifying that user/password combination. If that doesn't work, you need to diagnose that first.
April 2, 2008 at 10:48 am
LIMSUSA is there if I log in under sa and not what we used have the ODBC user set up as. But even if I change the ODBC user to sa it still doesn't work. But LIMSUSA does exist in the security area.
April 2, 2008 at 11:32 am
That is strange...What do you see when you run this..
sp_helprotect null,'LIMSUSA' ----in the database you are trying to connect.
Can you try to drop and recreate this login from security tab. make sure to uncheck password policy while creating new login.
If that doesn't work Can you create new DSN using SQL Native Client not ODBC and let us know the error message if it is'nt working.
April 2, 2008 at 11:41 am
OK gang thank you so much for your help.
The problem was our front end has a password saved in it. SQL 2005 is case sensitive, but when the permissions and passwords got sent over they didn't match properly. So even though the username was tied correctly to the userID the password wasn't the same so it didn't appear "orphaned" but actually was!
I was able to get it set by setting the password, once I found out what that was!!
But thanks again for all those that were trying to assist me!! I did have to do the sp_revlogin to get it over to begin with and the procedures you took me through were straight forward and very helpful to know that I had the SQL side correct and that is was the Front client that was having an issue!
Again thank you!
April 2, 2008 at 1:38 pm
you are welcome and thanks for the update.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply