Connection String in .NET program Config File
Connection String in .NET program Config File
(OP)
I have this connection string in my .Net app config file.
This works from the server where SQL Express 2014 is installed.
It fails from another server.
Here's what's confusing me.
The server name is DS2.
The SQL instance name is DS2\SQL14EXPRESS.
I was getting the error: error 26, Server was not found or was not accessible.
Now I'm getting Server is not responding.
As far as I know I have the SQL TCP/IP protocols enabled in the configuration manager.
Did some googling and found several links about the back slash in the instance name.
Not sure if this is the cause of the error.
I tried many different combinations for the data source, but none have worked.
Any ideas or suggestions would be appreciated.
CODE
<add name="Ticket1.My.MySettings.TicketConnectionString" connectionString="Data Source=DS2\SQL14EXPRESS;Initial Catalog=Tickets;User ID=Ticket_User1"
This works from the server where SQL Express 2014 is installed.
It fails from another server.
Here's what's confusing me.
The server name is DS2.
The SQL instance name is DS2\SQL14EXPRESS.
I was getting the error: error 26, Server was not found or was not accessible.
Now I'm getting Server is not responding.
As far as I know I have the SQL TCP/IP protocols enabled in the configuration manager.
Did some googling and found several links about the back slash in the instance name.
Not sure if this is the cause of the error.
I tried many different combinations for the data source, but none have worked.
Any ideas or suggestions would be appreciated.
Auguy
Sylvania/Toledo Ohio
RE: Connection String in .NET program Config File
Not sure at which stage of all of this you are. First step is to use SQL Server Configuration Manager to turn on TCP/IP, because SQLExpress starts with only allowing connections via Shared Memory protocol, that means Shared RAM access, and that's only possible on the same computer.
Then you should also know about the SQL Browser service.
https://learn.microsoft.com/en-us/sql/tools/config...
Chriss
RE: Connection String in .NET program Config File
I will review to see what I may have missed.
Auguy
Sylvania/Toledo Ohio
RE: Connection String in .NET program Config File
How about posting a few screenshots of what you have configured for all the different nodes?
Chriss
RE: Connection String in .NET program Config File
Will get back to it tomorrow.
Thanks again.
Auguy
Sylvania/Toledo Ohio
RE: Connection String in .NET program Config File
1. usually on (dev) clients using SSMS. In the connect dialog click on the Servername:"<browse for more>" and then pick the DS2 server.
2. usually on SQl Server machines (on other instances in the domain, for example): Using sqlcmd - L
Describd in more detail here:
https://solutioncenter.apexsql.com/how-to-get-a-li...
If you don't see the DS2\SQL14EXPRESS server from whichever computer should see it, there still is something misconfiguered. Or the config isn't actively used. A reconfiguration mqay only be applied when the service restarts, i.e. making the right config, but not stopping and restarting the sql serivice won't change its network visibility.
Chriss
RE: Connection String in .NET program Config File
On the server, run ipconfig in a command prompt and take note of the IP address for the server (DS2).
Then, try changing the connection string to use the IP Address instead. Ex: 192.168.1.13\SQLExpress
If you can connect using the IP Address but not the computer name, there is definetly a DNS problem on the network. If this does not solve the problem for you, let me know and I will show you how to bypass the need for the SQL Browser service by using the port directly.
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
RE: Connection String in .NET program Config File
Fortunately we can get to it another way at this time, but I will need to get it done soon.
Auguy
Sylvania/Toledo Ohio
RE: Connection String in .NET program Config File
Sorry it took so long to reply.
Took me a while to find it, but SQL was listening on a different port than 1433.
Once I changed the firewall setting use the new port it worked.
I thought I had the correct port, but after re-reading the above posts, I went back to the port being the problem.
Thanks again!
Auguy
Sylvania/Toledo Ohio
RE: Connection String in .NET program Config File
1. You need to make sure that the SQL Browser service is running on the database server machine. The SQL Browser service converts an instance name to a port number. If you use default port 1433, you do not need to have the SQL Browser service running.
2. You need to make sure there is an exception in your firewall for the SQL Browser service. The Browser uses UDP port 1434.
3. You need to make sure there is an exception in your firewall for whatever tcp port you are using for your SQL instance.
4. If there is a router between your client machine and your SQL Server machine, you probably need to set up port forwarding on your router. For example, if you wanted to connect to a database on the internet, you would need to set up port forwarding because you cannot directly connect from the internet to a SQL Server maching.
There is quote a lot of flexibility in your connection string, more than most people realize. I'm specifically referring to the Data Source part.
CODE
connectionString="Data Source=DS2\SQL14EXPRESS;Initial Catalog=Tickets;User ID=Ticket_User1"
If you are using a LAN, you could do...
Data Source=DS2\SQL14Express
Data Source=192.168.1.20\SQL14Express -- using the actual IP address for the SQL Server Computer
Data Source=DS2\1433 -- using the actual port number the sql server instance is using
Data Source=192.168.1.20\1433 -- using the actual port number the sql server instance is using
If using the computer name does not work, but the IP Address does, you probably have a DNS problem on your lan.
If using the instance name does not work, but the port number works, you probably have a problem with the SQL Browser service.
If instance name does not work and port number does not work, you probably have a firewall problem.
If you wanted to connect through the internet:
Data Source=www.YourURL.com\1433
In this case, I think you need to use a port number instead of instance name (but could be wrong). The URL would point to an external router, where you would need to forward the port to an internal IP Address.
I have successfully accomplished all of these scenarios, so I know it works. Years ago, I did have SQL servers accessible through the internet, but have since shut those connections off for security reasons.
Hope this helps.
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
RE: Connection String in .NET program Config File
I will save and review as I need it.
Thanks
Auguy
Sylvania/Toledo Ohio