MS SQL Connection - Dreamweaver (ASP)

Report · Oct 28, 2009

Hi - I'm using the connect string below to connect to my MS SQL DB (all local). When i test the recorsets in Dreamweaver, all works fine. But when i browse the .asp page, i get this error!

"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "mydatabase" requested by the login. The login failed."

As far as i'm aware, i have setup the MS SQL Database (migrated from Access DB as 'Windows Authentication")

Anyone else experienced the same or knows what the problem is?

Thanks in advance -

Andy

-------connection string--------
Dim MM_conn_STRING
MM_conn_STRING = "Driver={SQL Server}; Server=WEBDESIGNAH\SQLEXPRESS; Database=mydatabase; UID=; PWD=;"

Report · Oct 28, 2009

Your UID and PWD are blank in your code below. SQL server requires UID's and PWD's.

Report · Oct 29, 2009

Hi bregent - thanks for replying.

I log in to my MS SQL Server Management Studio using Windows Authentication so i'm guessing i should be using my normal windows username and password in the connection string? I have tried this but get the error below:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'hughes.andy'.

If I leave out the UID and PWD, i can connect to the database through Dreamweaver and all recordsets work when tested. But when i view the site through IIS, i get this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "mydatabasename" requested by the login. The login failed.

*mydatabasename replaced my real db name

Thanks

Andy

Report · Oct 29, 2009

Just to give an update on my previous post:

I removed the UID and PW from my connection string and unchecked 'Anonymous Access' in IIS. Integrated Windows Authentication IS checked.

My recordsets in Dreamweaver are working and i can now view the site through IIS

I'm happy all is working locally, but i'm sure i'll come accross similar problems when i deploy the site to a live server.

Thanks for posting 🙂

Andy

Report · Oct 29, 2009

>I removed the UID and PW from my connection

>string and unchecked 'Anonymous Access' in IIS.

>Integrated Windows Authentication IS checked.

Yes, that is correct. From http://support.microsoft.com/kb/247931

"In the ActiveX Data Objects (ADO) connection string, omit the parameters "UID" and "PWD" when you use ODBC, and "User ID" and "Password" when you use the SQLOLEDB provider."