Connecting to SQL Server Express from an ASP Application

I have an ASP application which is currently using a SQL 2000 Database, attaching via SQLOLEDB. I am getting ready to migrate my ASP application to .NET using SQL Server 2005 Express. The only problem, is that I am unable to connect successfully from my ASP applcation to my DB under SQL Express.

I have taken all of the steps that I have read, including the blog by Ming Lu. I still cannot be through.

I have included both the messages that are thrown and comments about the drivers and connection strings.

I have enabled TCP/IP and Named Pipes for SQL 2005 EE. I have also included the executable, port 1433, port 135, etc. in the firewall exceptions.

I have defined the DB in ODBC using both the Native Sql driver and the SQL Driver. Connection authentication is via SQL Server Authentication.

If anyone has any input, I would be grateful.

Error Messages:

Named Pipes Provider: Could not open a connection to SQL Server [53]. - using SQLNCLI - SQL Native Driver

::[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. - using SQLOLEDB - SQL Native Driver

::[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. - using SQLOLEDB - SQL Server Driver

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

connection string for SQLOLEDB: "PROVIDER='SQLOLDEB';DATA SOURCE='.\SQLEXPRESS';User ID='Abilities2005';Password='abilities';Initial Catalog='nbdc2005';"

connectdion string for SQLNCLI: "PROVIDER='SQLNCLI';DATA SOURCE='.\SQLEXPRESS';User ID='Abilities2005';Password='abilities';Initial Catalog='nbdc2005';

source name varied from .\SQLEXPRESS, localhost\SQLEXPRESS and machinename\SQLEXPRESS

Thanks, Tom

Did you enable Remote Connections to the Server, everything else than Shared memory will need remote ocnnections. See more details and a Screencast on my site under the screencast section.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Jens, thanks for the tips, but, remote connections were already enabled on my machine. And, I also connected to the db using the sql server logon and pswd that I was using in my script.

That is not the issue. Any other suggestions for me?

I am relatively new to the MS Suite of products, so everything right now is a learning experience for me.

Thanks, Tom Avogardo

|||Is SQL Browser activated on the machine, is a firewall enabled, is the SQL Server specified on another port than 1433, then you will probably have to name the port after the instance name: .\Instance,Portnumber (But you only need this if SQL Browser is not enabled)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Jens, thanks for the reply.

SQL Browser is active and the default TCP port is 1433. I tried using a named port to see if it would do any good, again, no go. I have coded 1433 in the firewall, as well as adding the instance of sqlsever to the exception list. Still no go. And if I switch back to my SQL 2000 DB connection and DB, no problems.

Driving me nuts here. I want to convert over to .NET and SQL Express but before I can do that I have to have SQL 2005 working on an every day basis with no problems.

Any more suggestions would be welcome.

Tom|||I had the same problem. Here is a solution:

Open SQL Server Configuration Manager and find Protocols for MSSQLSERVER, enable TCP IP, then open its Properties, then switch to tab IP Addresses and explicitly enable all IPs. It will require to restart SQL Server service. Then enjoy the connection.

Hope this helps.|||

Hi, thanks for the answer. I tried what you suggested, it did not work for me.

However, I did come accross a different Data Source= configuration in one of the mdsn libraries. Instead of the =.\SQLEXPRESS or =Servername\SQLEXPRESS, it was =.\\SQLEXPRESS.

I tried this change and attached immediately.

I do not know if it was just the datasource change or both, but I do know that it works.

Thanks again for everyones help, Tom

|||

Hi,

ok, just for the case that you are using a coding language which has escape characters to determine special character commands (like in .NET languages the \ announces an escape sequence) you can (in the most cases) skip these special characters with typing in a override escape command (in .NET it would be @. --> @."S\r" would be --> "S\r" instead of being --> "S<CR>" (because \r indicates a line feed).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de