Why do I see 'User sa Not associated with Trusted...' 1

[TroyMcClure]

Ok, I'm not big on sql 2000 administration, but we just set it up and gave sa a password, and I'm positive I've got it right. Now when I go to EM and try to Connect as sa, I get the error. It's fine if I do Windows Authentication, but I want to know why the user sa doesn't seem to have rights to a the server of which he has all rights, etc.

I'm guessing it has something to do with how sql server is doing authentication--but I have 2 questions:

1. How do I change it so I can set up odbc connections using login names such as 'sa' or others I create?

2. What is the difference between the two and what is meant by 'trusted connection'?
Thanks,
--james

 

[SQLBill]

SA can ONLY connect using SQL Server Authentication. If your server is set for Windows Authentication ONLY, then you cannot use the SA account.

Trusted means that SQL Server 'trusts' the authentication (login and password) that is used by the operating system (your Windows Login). This is Windows Authentication Only in SQL Server. The nice part of this is that you only need one login/password, the one that you use to log into the computer. Bad part is that the SA account doesn't work.

The other type of authentication is Mixed Mode (Windows Authentication and SQL Server Authentication). This allows both trusted and untrusted logins. With untrusted, you log onto your computer and then SQL Server asks you for a login and password to access it (the SQL Server).

Open Enterprise Manager, right click on the server name, select Properties, go to the Security tab. That's where you can change the authentication.

Next, you need to make sure your users have a login on SQL Server. In Enterprise Manager, drill down to Security, expand that and click on LOGINS. Are your users listed there? If not they need to be added. If they are, then right click on the login name, select Properties. In the General window is where the authentication type is set.

Setting authentication for logins. Once set you have to delete the login and re-add it to change the type of authentication. You CANNOT set a login to an authetication that the server is not set for. So check that first. (if the SQL Server is set for Windows Only, you cannot set a login to be SQL Server authentication.)

-SQLBill

 

[TroyMcClure]

SqlBill,
Thanks, that was a perfect explanation!
--james