Logon failure (driving me mad) 1

[metamind]

Hi,

Problem
=======

I can use Windows Authentication to get access to my db through SQL Query Analyzer, but not SQL Server authentication.

I want to connect using SQLUser (see below)

Details
=======

I have just set up a new sql server on Windows 2003 Small Business Server with a domain (MyCoDomain). I have set it up to have mixed authentication:

Enterprise manager -> (local) -> (right click) properties -> security (tab) -> authentication -> (selected) SQL server and Windows

I have a special user for connecting to the database (SQLUser) with a password (SQLUserPw).

The MSSQLSERVER service is set to start automatically on startup using SQLUser and SQLUserPw:

Services -> MSSQLSERVER -> (right click) properties -> General -> Startup Type -> Automatic

and

Services -> MSSQLSERVER -> (right click) properties -> Log On -> (selected) This account -> MyCoDomain/SQLUser with password = SQLUserPw

The service starts up OK on system startup.

SQLUser is a member of the domain administrators group. Both BUILTIN/Administrators and MyCoDomain/SQLUser are present in Enterprise Manager -> (local) -> Security -> Logins. (For some reason [Enterprise Manager -> (local) -> Security -> Logins -> (right click) Properties -> General tab -> Authentication], which has two radio buttons is disabled and fixed on "Windows Authentication", MyCoDomain.)

Thanks,

 

[SQLBill]

BUILTIN/Admins are for TRUSTED accounts only (Windows Authentication).

Your MyCoDomain/SQLUser was set to use Windows Authentication only when it was created (This is because the SQL Server services use that account). You can not change how it authenticates without deleting it first.

Now, no one should be using the SQL Server services account to log into SQL Server. A service account should ONLY be used by that service. Create a new account and set it to use SQL Server authentication.

-SQLBill

 

[metamind]

Bingo!

SQLBill, you are a great man.

Thanks

 

[SQLBill]

I don't know about great...but thanks for the star.

-SQLBill