SQL Server db connection not working - URGENT Pls

[Guest_imported]

I wrote my asp code with access db connection. Everything works great.
Now, we are converting our db from access to sql server db.
But now, I can't connect to sql server db.
Please take a look at the access db connection syntax followed by that of sql server and tell me why I am getting this error.
How can I fix the error?

--This is access connection: this works fine

set conn = Server.CreateObject("ADODB.connection&quot
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("\Boards\db\aboard.mdb&quot & ";" & _
"Persist Security Info=False"
conn.Open(sConnection)


--This is sql server connection syntax: This is the one
--failing!
set conn = Server.CreateObject("ADODB.connection&quot

Conn.open "DSN=boards;UID=myuserid;PWD=mypwd"

--This is the error message I am getting!!
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'PW2\IUSR_PW2'.
/boards/default.asp, line 21

 

[Tarwn]

Make sure you set up a user under SQL Server named myuserid with password mypwd. Then make sure you have given them access to your database you are trying to access. You will need to switch the type of authentification from Windows Authentification to SQL authentification, the username displayed above is a standard windows looking username, which means it is trying to use windows authentification rather than the username/password you are giving it.
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[Guest_imported]

Tarwn,
Thanks for the quick response.
I want the authentication to be that of windows, rather than sql server.
The idea is that we don't want certain groups access our database using the sa login.
What is strange is that I was able to use this syntax:
set conn = server.CreateObject("Adodb.Connection&quot
conn.Open "DSN=Quotes; " & _
"uid=myuserid;pwd=mypwd"
Set rsDIDU = server.CreateObject("Adodb.Recordset&quot
to get connected to sql server yesterday. It is not working today along with anything else I have tried.

 

[lucasm]

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q247931&

I had a similar problem today, and ended up using an Administrator login in Directory Security for the folder that contained my page - but if you don't anonymous access you probably want 'Basic Authentication' in Directory Security with no UID and PWD in your connection string.

 

[Tarwn]

The SQL Server sa issue was for people who were to lazy to put an sa password in the system. The "backdoor" talked about so much was simply taking advantage of a SQL Server that had no sa password, by setting your password you don't have to worry about people using this againast you because most of them try sa with a blank password, fail, and move on. According to my firewall records the number of people looking for that particular hole is growing fewer, as I no longer get the number of hits on my SQL Server as I used to.
-Tarwn
------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[Guest_imported]

Tarwn,
while I do not particularly disagree with that statement, my issue here is that of management issue.
I have been with this company now for just 3 weeks so you can see that I am fighting an uphill battle trying to convince them to abandon the notion forced up on them by the network administrator who feels the db will be more secure if everyone uses windows authentication method.
He is not here today; perhaps if he gets back tomorrow, we might resolve this.
Eventually, with the help of today's setback, I can make a stronger case about the need to take another look at a simpler solution such as appending a password to sa thereby extinguishing the need to log in with sa.

 

[Tarwn]

I am not arguing the case for either, but from the security standpoint I am not sure how windows authentification is that much stronger as you are basically authorizing the web server to have access. If the web server is compromised than with either authorization method someone can gain access to your database. I've always preferred the sq authorization as long as the web server is on the same machine, the major danger comes in sending an SQL login over a network where it can be sniffed.
No matter which way you go, sa has to have a password. Never leave an account like that open even if today your using windows authetification. A month or two from now if someone decides to change it and does not know the sa account is unprotected they will be opening the server wide open, it's one of those better-to-be-taken-care-of-while-you-remember things.
I am just lazy and never spent the time to get my windows authentification working right, I just make sure my firewall is setup only to allow my webserver and personal applications to connect to it, everything else is blocked.
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation