June 10, 2004 at 7:29 am
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.
June 10, 2004 at 7:51 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 10, 2004 at 9:07 am
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
June 11, 2004 at 7:10 am
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.
June 12, 2004 at 4:24 am
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
June 15, 2004 at 5:51 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply