Recommended MSSQL Connection String on C# MVC .NET

Recommended MSSQL Connection String on C# MVC .NET

dpanscikdpanscik Posts: 202Questions: 47Answers: 0

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?

Answers

  • allanallan Posts: 63,237Questions: 1Answers: 10,418 Site admin

    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

  • dpanscikdpanscik Posts: 202Questions: 47Answers: 0
    edited June 23

    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?

  • kthorngrenkthorngren Posts: 21,184Questions: 26Answers: 4,925
    edited June 23

    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

  • dpanscikdpanscik Posts: 202Questions: 47Answers: 0
    edited June 23

    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.

  • kthorngrenkthorngren Posts: 21,184Questions: 26Answers: 4,925
    edited June 24

    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

  • allanallan Posts: 63,237Questions: 1Answers: 10,418 Site admin

    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

  • dpanscikdpanscik Posts: 202Questions: 47Answers: 0
    edited June 24

    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.

  • allanallan Posts: 63,237Questions: 1Answers: 10,418 Site admin

    Easy done! We've all been there :)

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

    Allan

Sign In or Register to comment.