Connection String in .NET program Config File 2

[Auguy]

I have this connection string in my .Net app config file.

<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

 

[Chris Miller]

localhost\SQLExpress is the default instance name of SQL Express. localhost then can be replaced by DS2 to access from somewhere else. But this is not searched like a file in the file system, it's a service, and to discover a service on another server there is a service called SQL Brwoser. Since an Express instance is normally only used locally, that might not run or not even be installed. But first of all the Express instance must allow connections by TCP/IP before you can even reach it from another computer, no matter if client or another server.

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/s...-server-browser-service?view=sql-server-ver16

Chriss

 

[Auguy]

Thanks for the reply, I think I did most of that.
I will review to see what I may have missed.

Auguy
Sylvania/Toledo Ohio

 

[Chris Miller]

Yes, you said, but then the behavior you describe matches perfectly to what I'd expect from a server instance that's not accepting incoming TCP/IP connections. Including that you're finally able to see the instance but not connect to it. Pay attention the configuration managerr has several nodes, some also for 32bit instances and some for client configuration. That's not configuring how the instance is available, but how clients are able to connect, i.e. SQL Server Native Client 11.0, that is the connection drivers (ODBC) and not the server itself.

How about posting a few screenshots of what you have configured for all the different nodes?

Chriss

 

[Auguy]

Thanks, working on another project today.
Will get back to it tomorrow.

Thanks again.

Auguy
Sylvania/Toledo Ohio

 

[Chris Miller]

If you're back and have time for it: To get an overview of which sql server instances are discoverable on some computer (no matter if client or server), there are mainly 2 ways:

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/...e-sql-server-instances-on-your-local-network/

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

 

[gmmastros]

There are a lot of reasons why this might fail. Chris mentions many of them. I have a customer that was having a similar problem connecting to SQL Server. After hours of digging, turns out they had a DNS problem.

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

 

[Auguy]

Thanks, haven't gotten back to this yet, hopefully tomorrow.
Fortunately we can get to it another way at this time, but I will need to get it done soon.


Auguy
Sylvania/Toledo Ohio

 

[Auguy]

Thanks to all who helped me on this issue.

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

 

[gmmastros]

You can use whatever port you want, but there are some configuration settings that you need to set.

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.

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=

http://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

 

[Auguy]

Thanks, that is great info.

I will save and review as I need it.

Thanks

Auguy
Sylvania/Toledo Ohio