my connection string doesn't work if I deploy my program to a different machine

Question

I have a c# application that works locally but it wont work if I publish my program on another machine. I get an error saying the server name cannot be found.

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in ProductionOrderQuery.exe

Additional information: 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)

Both machines are on the same corporate domain network. it may be something to do with how I am specifying my server name? Do I have to simply write the server name or do i need the fully qualified domain name?

The connection string is

Server=hostname\\SQLEXPRESS;Database=mydb;Trusted_Connection=True;

I'm using SS auth with sa account.

Do I need to do anything on SSMS to get this to work?

Edit: So I've since followed guides on how to connect by IP Address to see if it was DNS screwing it up. I have configured named pipes and static ports and changed my connection string. Firewall is disabled Still can't connect remotely, everything works ok locally. I did manage to get one machine to connect remotely and that's it, very puzzling.

New connection string is Data Source =

<ip>,1433; Network Library = DBMSSOCN;Initial Catalog = footfall; User ID = sa; Password=password;connection timeout=0;

I can ping the server from the machine I'm trying to connect from and nslookup reports correct hostname. I can run a trace from client to server fine. Firewall is disabled on server for testing.

Edit 2: When somebody signs in to a remote machine that isn't me, they cant connect. SO it seems there is still some kind of windows authentication going on here despite me specifically using the SA account. When I sign in, I can connect ok.

SOLUTION: Bit of a wild goose chase here but turns out I had used a data source wizard to populate a combobox in design view that was using a different connection string.

You cant just remove datasources either from VS which is very annoying. Took me a while to remove them.

I don't understand why though if the data sources are not bound to anything on the form, the app still insists on connecting to them at runtime.

Once I managed to remove the data sources, and not use the SA account (I created a new account with read write perms on my database) the application worked.

I dont understand why I cant add the sa account to my database (receive an error when I do so) but a custom account I can add fine.

So to summarise, in the end there were 2 issues, the fact I was using the SA account and the hidden connection string in one of the data sources linked to the combo box

Thanks.

Answer 1

Bit of a wild goose chase here but turns out I had used a data source wizard to populate a combobox in design view that was using a different connection string.

You cant just remove datasources either from VS which is very annoying. Took me a while to remove them.

I dont understand why though if the data sources are not bound to anything on the form, the app still insists on connecting to them at runtime.

Once I managed to remove the data sources, and not use the SA account (I created a new account with read write perms on my database) the application worked.

Answer 2

Please check "SQL Server(MSSQLSERVER)" service on second machine it should be started.

You can type services.msc in run window and find "SQL Server(MSSQLSERVER)" service name and just make sure it is started.enter image description here

Answer 3

I guess this is a Firewall Issue. Disable any antivirus or firewall installed in the second machine and check out if you are able to connect.

Answer 4

I had the same issue sometime. This is what worked for me:

Check the settings in the SQL Server Configuration Manager

"SQL Server Configuration Manager"

Click on "SQL Server Network Configuration" and Click on "Protocols for Name"

Right Click on "TCP/IP" (should be Enabled). Click on Properties

On "IP Addresses" Tab go to the last entry "IP All"

Enter "TCP Port" 1433.

Now Restart "SQL Server .Name." using "services.msc" (winKey + r)

Answer 5

You can try this ...

Confirm that server authentication is in mixed mode, like in image bellow.

Confirm that sql server is allowing for remote connections:

Confirm that sa user is in your database users list and/or is associated with your database dbo role. Just in case, also try to check if sa is db_owner for that database.

I would continue using a default connection string:

data source=server\SQLEXPRESS;initial catalog=db;integrated security=false;user id=user;password=pwd;Persist Security Info=True;

The persist security is an important part when using user/password to authenticate.

Another thing is that in your question you are adding 2 slashes to the SQLEXPRESS instance, when it actually is just one.

Answer 6

lets take your server name:

DESKTOP\SQLEXPRESS

then you can avoid your machine name in connection string like this.

Data Source=.\SQLEXPRESS

Thank You!

Answer 7

Please go to the SQL Server Management Studio and click connect button to connect with database engine. After that copy server name and past it to place of server name in the connection string.

follow the image

Thanks