Connection to SQL Server from IIS

Question

I have two Windows Server 2012 machines. They are both part of a domain I have created. One is a web server and the other is a db server hosting SQL Server 2012. From the web server I can establish a connection to the DB server using MSSMS but from an application I have written hosted in IIS I am unable to establish a connection. I get the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. 
The server was not found or was not accessible. 
Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I have verified that the SQL Server allows for remote connections.

I have verified that it is using the TCP/IP protocol.

I have tried fully qualifying the SQL Server machine name to include name.domain.com.

I am able to ping the SQL Server with no problem.

As I already stated I am able to establish a connection from MSSMS with no problem using both machine name and IP address.

I have shut down the Windows Firewall on both machines at this point and have added the rules that opened the following ports:

TPC/IP - 135, 1433, 1434, 2382, 2383, 4022

UDP - 1434

I even opened the ports to public just in case.

Within my application I have a static class that gives me the connection information. It looks like this:

    static public class _ConnectionData
{
    public static string Source = "192.168.0.33"
    public static string Catalog = "dbCat";
    public static string User = "dbCatUser";
    public static string Password = "**********";
    public static string ConnectString
    {
        get { return "Data Source=" + Source + "; Integrated Security=false; Initial Catalog=" + Catalog + "; User ID=" + User + "; Password=" + Password; }
    }
}

I set up my Virtual Directory in IIS by just copying the app directory under inetpub\wwwroot and then in IIS admin converting it to an application.

I am at a loss at this point. Is there something special I need to do to get a connection from within an IIS hosted app? Do I need to specify a provider as part of my connect string? Are there special permissions within IIS that need to be set? My appPool is set to Integrated, is that correct?

I am already bald so at this point I am not sure what I will pull out maybe arm pit hair.

Try your exact code from outside of IIS - in say a console app - does that work? (Instead of using SSMS). If so, then there may be restrictions on your IIS security (e.g. app pool account) reaching the DB svr. — Murray Foxcroft
– Murray Foxcroft, Commented May 19, 2016 at 16:16
Hi pachy, my first port of call with the application would be take "Integrated Security" out of the string all together. If you are passing a username and password combination this isn't needed. Next, as Murray said (beating me by a few moments) I'd try connecting with a console app, or even powershell ensuring the connection string is identical. — Pheonyx
– Pheonyx, Commented May 19, 2016 at 16:18
Are you connecting to the default or named instance? Does changing from the IP address to the hostname help? — Cam Bruce
– Cam Bruce, Commented May 19, 2016 at 16:37
I will put the code into a console app and try it right now. I have tried the connect string without the Integrated Security flag and that made no difference. I am connecting to the Default instance on SQL Server. I have tried putting MSSQLSERVER as the instance as well and it did nothing. I have tried both IP and hostname with no difference. Writting console app now. — pachyderm94
– pachyderm94, Commented May 19, 2016 at 17:15
console app worked like a champ. So what permissions would you guys look at for the app pool account? — pachyderm94
– pachyderm94, Commented May 19, 2016 at 18:09

Vahid Farahmandian · Accepted Answer · 2016-05-19 21:55:23Z

1

Error 26 has two main reasons. 1) SQL SERVER(XXX) service is not started, 2) connection string is not valid. In your scenario, reason 1 can not be correct, as you said that you can connect to Sql Server using SSMS. so the only thing left is reason 2. You need to check your connection string. To achieve this please validate your connection string using the link below:

Here

Also when you do not need to use Windows Authentication mode, you do not need to specify the Integrated Security=false

answered May 19, 2016 at 21:55

Vahid Farahmandian

6,6227 gold badges48 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pachyderm94 · Accepted Answer · 2016-05-24 18:57:23Z

So it turns out that I got caught using the wrong page directive (for me) with my .Net pages. I am used to using a src attribute and not a codebehind attribute. With the src attribute anytime the .cs file changes the class is re-compiled. With the codebehind attribute .Net looks in the dll first (I think). Since my connection information was coded directly into a static class that was not re-compiling it never saw any of the changes I made. I marked Vahid's answer as correct because it was that the connect string was still wrong I just did not know it.

I fixed the issue by moving my connection information to the web.config and reading it into the static class variables if they are null.

I would like to thank everyone who helped me out with this issue. it is greatly appreciated.

Collectives™ on Stack Overflow

Connection to SQL Server from IIS

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related