Connect to local SQL Server Express 2008 instance from classic ASP, using SQLOLEDB

1
votes

I am trying to set up a development testing server, Windows 2008 Server with IIS and SQL Server 2008 Express. The IIS and classic ASP (yeah, supporting antique projects) works fine, including connecting to remote SQL Server 2008 servers, via SQLOLEDB.

However, I fail to connect to the local SQL Server 2008 Express instance. It is installed as an instance (ie, PC-NAME\SQLEXPRESS), and user/pwd authorisation is enabled. I can successfully set up an ODBC entry, so the server works fine. The problem lies in getting the IIS-ASP-SQLOLEDB see the server and connect to it.

Simple connection string, which works on any remote SQL Server 2008 servers, fails at my local express server:

Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;User ID=userName;Password=_pwd_;Database=DbName

The error I get is

SQL Server does not exist or access denied.

The server error log does not contain any trace of unsuccessful login attempt, so I assume there is some problem resolving the data source (because of the instance name?), or the server is somehow inaccessible (protocol, security settings?) from the IIS process.

1
sql-serverasp-classicoledb
This may help connectionstrings.com/sql-server-2008 – Dee
In the Inetpub/wwwroot/uraspapp, did you grant permission to network service? In Sql management studios can you login to the sql server with username and password? May be you should include the full name of database in your connection string (Data Source=PC-NAME\SQLEXPRESS;) – demo.b
demo.b: I'm not sure where should I grant permission to network service. However, this app connects to remote SQL 2008 servers (full, not express) with no problems. Yes, I can log on to SSMS using user/pwd. – Passiday
Oh god, giveth me more wisdom in the new year! I tripped over the silliest error of all, I did not escape the backslash. So I decided to humiliate myself and come back here to report it so that I never do this again. And perhaps some poor lad will find this post someday and will realize that he did the same thing. – Passiday
Rested eyes are more efficient well done. – webb

1 Answers

0
votes

In SQL Server 2008 express edition it runs on dynamic ports. The solution is just go to properties of the TCP/IP and in the TCP Port just give whatever port you want your SQL Server to listen on. 

strConn = "Provider=SQLOLEDB; Network Library=DBMSSOCN; Data Source=.\SQLEXPRESS;     Connection Timeout=15;Packet Size=4096; Initial Catalog=DbName; User ID=userName; Password=_pwd_;"

Can you post how you access the connection object via ASP?