web.config and ODBC Driver 17 for SQL Server RSS

• • Reply
  Metzoid

  None

  0 Points

  2 Posts

  web.config and ODBC Driver 17 for SQL Server

  Sep 28, 2020 07:08 PM|Metzoid|LINK

  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?

• • Reply
  mgebhard

  All-Star

  53041 Points

  23612 Posts

  Re: web.config and ODBC Driver 17 for SQL Server

  Sep 28, 2020 08:06 PM|mgebhard|LINK

  Metzoid

  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/

• • Reply
  Metzoid

  None

  0 Points

  2 Posts

  Re: web.config and ODBC Driver 17 for SQL Server

  Sep 29, 2020 06:40 AM|Metzoid|LINK

  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.

• • Reply
  mgebhard

  All-Star

  53041 Points

  23612 Posts

  Re: web.config and ODBC Driver 17 for SQL Server

  Sep 29, 2020 10:27 AM|mgebhard|LINK

  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. 

• • Reply
  PatriceSc

  All-Star

  48510 Points

  18072 Posts

  Re: web.config and ODBC Driver 17 for SQL Server

  Sep 29, 2020 11:57 AM|PatriceSc|LINK

  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>