Help-Problem with Sql Server Authentication

Question

Help-Problem with Sql Server Authentication

uwin iwin

Valued Member

Points: 62
More actions
June 10, 2004 at 7:29 am

#82982

Hi,
I am using a Sql server 2000 Enterprise edition for my company project using asp., my project works fine with Windows authentication for the databasei created in sql server and while creating system dsn, but i need to add everybody as users and i dont think this as a feasible solution. When i try to use a system dsn where in i use the sql server authentication, i gave user name as "sa" and password as blank. but it says, "login failed for 'sa', not associated with trusted sql server connection". i searched in google for this, but did not find a feasible answer to my problem.
I have even created a new user with all rights using enterprise manager, access to all the databases. no go. still says same thing, but if i say windows authentication, it works. I cant even use my user id i created to even login to sql query analyser. I believe there must be a method to know / create the sql server login and password that can be used for system dsn.
Please help me how to create/ use a sql server authentication. I know how to use system dsn in asp and continue with it. Just the sql server authentication is causing me problems. I also have admin rights to the server, so it shouldnt be a problem.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

You need to go to EM, select the server, right click, properties. On the Security tab, select mixed (SQL and Windows) auth.

Then restart and you should be ok. DO NOT setup any DSNs or connections for your app using sa. Bad idea. Instead, go to Secuirty then logins and create a login and give it specific rights.

You could easily add all your users with their windows accounts if you put them in a Windows group and add that group as a login with specific rights.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Follow Steve's directions and you shouldn't have any problems (if you do, post them and we'll try to help) HOWEVER, you state:

i gave user name as "sa" and password as blank

This is BAD. the SA account should NEVER have a blank password. Fix that NOW.

Also, SA is not a Windows login, it can ONLY be used when SQL Server is set to allow SQL Server Authentication. The failure of SA to login is normally an indication that the authentication is set to WINDOWS Authentication ONLY. (Which is why Steve told you how to change it)

-SQLBill

uwin iwin Valued Member Points: 62 More actions · Answer 3

Hi there,

Thank yo so much, i tried and it worked, however, i created an other user and used it instead of sa. if i create a oledb connection string, it is working fine, but with system dsn it says null could not login, not a trusted sql server connection.

Since system dsn does work with windows authentication, but here i have chosen sql authentication and given the username and password. also put a check mark to login and test the connection, and it does say test completed successfully.

but using the system dsn in asp like normally i did with windows authentication is not working. May be does it require some other setting?

thanks.

johnb-28660 SSChasing Mays Points: 617 More actions · Answer 4

Hi

Try passing UserId and Password to your connection string in your ASP page(s) eg

conn.open "DSN=DSNName;UID=User;PWD=password;DATABASE="...

regards

Nic Washington Old Hand Points: 366 More actions · Answer 5

I was helping a user yesterday with setting his server up as Mixed Mode authentication - when ever he changed the setting via Enterprise Manager and then stopped and restarted the Server it reverted back to Windows Athentication. In the end I hacked his registry and set it up there.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer]

"LoginMode"=dword:00000002