Checklist to TroubleShoot SQl Server 2005 Connection Problem

I am trying to connect a production SQL Server 2005 which is under firewall through an Application written in ASP.Net 1.1 and I get error "SQL Server not available or Access Denied".

I am able to connect to the Server through SQL Mgr also using the same Code and Web.Config file I am able to Connect to the SErver using ASP.Net 2.0.

I am able to run the application for a SQL Server 05 which is in the same domain as of the Application Server.

My Application is not working only when I use ASP.Net 1.1 code for the Remote SQL Server 05 which is under Firewall. But for the same Server ASP.Net 2.0 works fine.

I have searched a lot for the problem and port No, DBNETLIB update is also tried.

Please let me know if anyone has came across such problem or knows the possible cause of the problem.

Sunil.

Can you post your connection string for both ASP.NET 2.0 and ASP.NET 1.1 app?|||

Sure, I am using the same connection String for both versions.

In Web.config I have added the following Connection String :

<add key="CnString" value="data source=<IP of Server>;User Id=sa;password=sa;Initial Catalog =<Database>"/>

Code from aspx file :

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["CnString"]);

conn.Open();

Response.Write("Connection Opened");

conn.Close();

When the same code is written in ASP.Net 2.0 it gets executed but through Asp.Net 1.1 its throwing exception as "Sql Server does not exist or Access Denied"

|||

Hi Sunil,

Is the ASP.Net 2.0 and ASP.Net 1.1 both run on the same machine? Is there more error message text available? Also, the obvious question is are you using the same IP address in both applications?

Another thing you can try is to force the app to TCP and see what happens. You can do this by prefixing the server name with "tcp:". FYI, .Net 2.0 will always try TCP first for remote connections while this isn't always the case for 1.1.

Il-Sung.

|||

Hi, Thanks for the "tcp" suggestion. I will check whether it works or not.

I have two applications both having the same code which I have posted above. One is developed in VS2003 and other in VS2005.

Both these applications are using same connection string and talking to same SQL 2005 server.

Also earlier we were using SQL 2000 server which was also under Firewall but it use to work with the above mentioned Connection String very well.

About error message dont have details but error Source is System.Data.SqlClient.

I will try the "tcp" option and will get back soon.

Thanks.

Sunil.

|||

Hello,

Our problem is solved. It happened because the user who shifted Databse from Sql 2000 to Sql 2005 was not DBA so direct procedure names were not working for the Database in 2k5. We gave DBO rights to the user and problem was solved.

Thanks to all of you for your time and suggestions.

Sunil.

|||

Following is the checklist we have prepared to be considered if anyone came across Connection problem with Sql Server 2005.

1) Is the Database is properly shifted from SQL 2000 to SQL 2005.

While shifting the Database from SQL 2000 to SQL 2005, strictly Administrator login tote used i.e. using "sa" login only. If this is not done then the schema

definition of the underlying Tables and Procedure changes from [dbo].Tablename to [LoggedInUserName].TableName and which will give error while using the

Database Objects.

2) Get confirmed from Client whether SQL Server is in same Domain as of Application Server or Not

If Application Domain and Sql Server Domain are different in that case there are Few things to be checked/enabled on SQL Server

Check whether Server is working on Windows Authentication / Sql Authentication

Is remote access allowed for Sql Server

Is the default ports 1433/1434 are opened

Is protocols TCP/IP and Named Pipes Enabled under Sql Configuration --> Protocols

Is Sql Browser service is running

Is proper permissions are granted for the Database to be used, for the user used in connection string ( if Sql Authentication is used )

If Windows Authentication is used the ASPNETUSER must be added to Logins in SQL Server Logins and must be given proper permissions to access the Database.