Connecting to SQL Server from ASP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Connecting to SQL Server from ASP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-29 : 08:50:36

Steve writes "I've just begun using SQL Server (2000), and have not got off to a very auspicious start! I've looked in your archives for the answer to what I'm sure must be a fairly common question from the inexperienced, but I've found nothing to help. So, here goes:

I cannot connect to a SQL Server database from an asp page. I keep getting the error message 'Login failed for user ' + MyUsername
My connection string looks like the one below, and the database uses Windows authentication.

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source=SERVERNAME;UID=MyWindowsLogOnID;PWD=MyPassword;"

Grateful for any help

Regards
Steve"

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-01-29 : 09:47:13

Hi Steve,

If your DB uses Windows authentication, is there any need to pass your username and PWD? I'm assuming you're running IE and your users have valid Windows accounts. If this is the case, they're already logged on and passing the username/pwd a second time should not be necessary.

Another shortcut to look into, if you have not done so already (again, I'm assuming you're running in a straight up Windows environment, with IIS as the web server), is to create the data source name (DSN) on the web server via ODBC Administrator (create a System DSN). This way you can simply refer to the DSN in the ASP code as:

DSN="myDB"

where myDB is a System DSN set up on the server. The data provider info and DB path are set up when you create the System DSN. I find this to be a cleaner solution than having to write out the connection path in the ASP code each time.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-29 : 10:00:49

"Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=MyDB;Data Source=MySERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MYSERVER;Use Encryption for Data=False;Tag with column collation when possible=False;User ID=MyID;PASSWORD=MyPassword;"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources