ASP/SQL Server Express: Could not open a connection to SQL Server [53]

1
votes

Windows 7, IIS 7.5, SQL Server 2008 R2, ASP classic (website I want to upgrade, but need to make it works).

connection string: 

Provider=SQLNCLI10; Data Source=.\SQLExpress; Initial Catalog=shoshvi_sql-db;Integrated Security=SSPI;Connect Timeout =1

I am getting this annoying error:

Microsoft SQL Server Native Client 10.0 '80004005'

Named Pipes Provider: Could not open a connection to SQL Server [53].

filename.asp, line 15

What I tried, as I remember:

  • Test connection using SSMS (works, to localhost and to other server in LAN)
  • Test connection using SQLCMD (works, to localhost and to other server in LAN)
  • Tried many datasources/servers ( name\SQLExpress, .\SQLExpress, .\SQLEXPRESS)
  • Tried to change settings in Start Menu\Programs\Microsoft SQL Server 2008\Configuration Tools, enable TCP/IP, Named Pipes, look all sub items and find wrong configuration. after any change, I was restarted service.
  • Tried to enable SQL Server Browser.
  • Tried to connect to other SQL Server in the same network, using SA and SA password (in the other "server" I could use IIS/ASP/MSSQL and works fine).
  • Tried to disable Windows Firewall
  • Tried to enable remote connections to SQL Server Express
  • Tried Integrated Security=SSPI
  • Tried Windows Administrator password
  • Tried change to TCP/IP (and get other error, same reason)
  • Tried to search google, and stackOverflow and other for an idea.
  • Tried similar questions here.

What more?

Thanks !

2
sql-serverasp-classic
did you try creating a sqllogin and password and use that instead of SSPI – rene
yes, and also tried use SA and Windows Administrator account. – Moshe L
You are intending to use the sqlexpress instance or is the db in the sqlserver instance? – rene
sqlexpress, the DB was attached with SSMS – Moshe L
Why do you have such a short connection timeout? – RickNZ

2 Answers

1
votes

Try this. And make sure you have TCP/IP enabled under 'SQL Native Client Configuration' on SQL Configuration Manager.

PROVIDER=SQLNCLI;DRIVER={SQL server};SERVER=127.0.0.1;DATABASE=shoshvi_sql-db;UID=username;PWD=password;
1
votes

Double-check that any ALIAS you're using is created properly.

I was encountering exactly the same error and it turned out to be that I hadn't created the alias used in a script when trying to run it on a new machine.