locked
web.config and ODBC Driver 17 for SQL Server RRS feed

  • Question

  • User1150377710 posted

    I am using the standard sqlclient in my web.config on my web server running on windows 2016 server. I have installed the odbc driver for sql 2017 on the web server (64 bit) so that it can connect to my sql server 2017 (64 bit). How do I get the web.config to talk using the correct driver as it is not working?

    Monday, September 28, 2020 7:08 PM

All replies

  • User475983607 posted

    I am using the standard sqlclient in my web.config on my web server running on windows 2016 server. I have installed the odbc driver for sql 2017 on the web server (64 bit) so that it can connect to my sql server 2017 (64 bit). How do I get the web.config to talk using the correct driver as it is not working?

    You should use the SqlClient Driver not ODBC.  You can tell the which driver you are using by looking at the connection string.

    https://www.connectionstrings.com/microsoft-odbc-driver-17-for-sql-server/

    https://www.connectionstrings.com/microsoft-data-sqlclient/

    Monday, September 28, 2020 8:06 PM
  • User1150377710 posted

    I have   </connectionString>     <add name="const" connectionString="Server=SQLP06\IMGT1,53415; Database=myDB; User Id=metzoid; Password=mypassword" providerName="System.Data.SqlClient" />     </connectionString>

    Continuing to get error 500 - internal server error. in .net when I use classic ASP to test my connection using odbc it connects fine etc. But in .net does not. The instance is IMGT1 on SQLP06 using port 53415. I have different ways in web.config but still get error 500.

    Tuesday, September 29, 2020 6:40 AM
  • User475983607 posted

    The simplest way to get the connection string is using Visual Studio to make the connection using SQL Server Object Explorer.  Then copy the connection string from the properties window. https://docs.microsoft.com/en-us/sql/ssms/object/connect-to-an-instance-from-object-explorer?view=sql-server-ver15

    The default SQL port is 1433.  I assume 53415 is the web application port?  If you've changed the default SQL Server port then your connection string should also use the port 53415.

    Lastly, a 500 error can be other issue with your code. 

    Tuesday, September 29, 2020 10:27 AM
  • User753101303 posted

    Hi, 500 is most often a server side exception in your code (by default you should have an error message in  the Windows event log).

    You can check also the IIS log to see if you have a substatus (for example 500.19 is rather a problem with the config file itself): see for example https://stackify.com/beyond-iis-logs-find-failed-iis-asp-net-requests/

    It doesn't make a difference but this is the ADO.NET Data Provider for SQL Server (which is the preferred approach) rather than ODBC : https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/data-providers

    Edit: btw this is a typo? You start tag include an extra / and you are missing the s. See for example:
    https://www.connectionstrings.com/store-connection-string-in-webconfig/

    Else when you copy/paste configuration or code please make sure to avoid introducting any unwanted change.

    Edit 2: so to clarify try with the following section inside your web.config file :

    <connectionStrings>
       <add name="const" connectionString="Server=SQLP06\IMGT1,53415; Database=myDB; User Id=metzoid; Password=mypassword" providerName="System.Data.SqlClient" />
    </connectionStrings>

    Tuesday, September 29, 2020 11:57 AM