Recommended MSSQL Connection String on C# MVC .NET

dpanscik

I have an older editor project running 2.1.1 that uses the following MSSQL connection string that is set in Properties Settings;
Server=tcp:database.com,1433;Database=DatabaseName;User ID=UserName;Password=Password;Trusted_Connection=False;Encrypt=True;

Today I am building up a fresh editor project running 2.2.2 the old connection string doesn't seem to work.

Is there by chance a new preferred sample database connection string that I should be using these days?

allan

There should be no change to how the database connection is made between 2.1.x and 2.2.x.

What error message are you getting?

Allan

dpanscik

Hi Allan,

I am getting the following error from Visual Studio;

I tried two different SQL servers, both have the same error, which leads me to believe the issue is somewhere else.

System.Data.SqlClient.SqlException: '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: TCP Provider, error: 0 - No such host is known.)'

and the following error from DataTable;

I wanted to make sure this wasn't a firewall issue with my local dev machine, or a local network issue so I published the project to the web server and have the same out on the web on the production server.

Also an interesting note. I am successfully connecting to the SQL server elsewhere in the project using ADO.NET Data Connection. Same server. The ADO.NET Data Connection works, the method used in DataTables, for some reason, has a connection issue.

What do you think?

kthorngren

No such host is known

That suggests a DNS issue where the name database.com can't be resolved to an IP address. Trying pinging database.com from your machine.

There error also mentions this:

SQL Server is configured to allow remote connections.

Verify the network configuration of the MSSQL server.

Kevin

dpanscik

Hi Kevin,

database.com is a obfuscation.

And keep in mind the ADO.NET connection works with using the exact same database FQDN.

I've also tested 2nd known database server and had the same issue. The 2nd database server connects elsewhere in the project using ADO.NET, but datatables database connection fails.

With all this said, the datatables database connection is experiencing a failure.

kthorngren

database.com is a obfuscation.

Thats fine. The same troubleshooting steps apply. Can you ping the server using the name/ip address defined in the config string?

Have you verified the MSSQL settings?

Maybe use traceroute to see where the routing failure occurs.

Can you connect to the server from the local server using the config string parameters?

It requires some basic network troubleshooting steps.

Kevin

allan

This is where the connection is made.

Looking at a "Blame" there haven't been any changes in this area of the code for quite sometime. Certainly nothing that I can see between 2.1 and 2.2.

If you use that same connection string from that same host, does it connect without using the Editor libraries? If it does, can you show me the code you are using for that?

The error message is quite clear that the host can't be found, so to doesn't immediately sound like a library issue to me - as Kevin says, it sounds like a networking issue from the webserver to the database server.

Allan

dpanscik

I am embarrassed to say but not to proud that I could not admit. I had a misspelling in the database's FQDN. I must have looked at it 10 times to confirm I had the properly spelled FQDN and missed the typo each time. Geez.

allan

Easy done! We've all been there

Great to hear that you've got it working now.

Allan