2

Earlier we were using default port number on the SQL Server and import of registry was working fine when configuring the clients to connect through ODBC. Recently we moved to new server where the SQL Server port is not the default one. We have around 300 client where this change needs to be made. Due to this we are looking for the best way to reconfigure the SYSTEM DSN's. The registry file content is as below,

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources]
"SQLtest"="SQL Server"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\SQLtest]
"Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
"Description"="SQLtest"
"Server"="BLR002\\MAINT"
"Database"="Maint"
"LastUser"="sa"

Please let me know how to include the specific port number as well into the registry or what is the alternative method that can be used.

1 Answer 1

2

To specify a non-default port number, add a comma and the port number after the server name. For example, with a port number of 12345:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\SQLtest]
"Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
"Description"="SQLtest"
"Server"="BLR002,12345"
"Database"="Maint"
"LastUser"="sa"

If the SQL instance is a named instance (named MAINT) like the DSN in your question shows, you should be able to connect using with your original DSN specification instead of port as long as the SQL Server Browser service is running on the server, UDP port 1434 allowed through the firewall on the server, and the instance port allowed as well.

3
  • Thanks for the response, was on a vacation hence couldnt response earlier. the UDP port we are using is Port no - 1436. In this scenario what is the best way. Please advise.
    – Rohit
    Nov 14, 2018 at 5:16
  • @Rohit, I think you mean SQL Server is listening in TCP port 1436 (instead of UDP port 1436). You can specify either "BLR002,1436" or "BLR002\\MAINT". In the latter case, the SQL Server Browser service must be running on the server and UDP port 1434 allowed so that the client can resolve the name "MAINT" to TCP port 1436 at run time. Of course, TCP port 1436 must also be allowed.
    – Dan Guzman
    Nov 14, 2018 at 13:17
  • thanks for the response it helped in overcoming the issue. We had to update the ODBC on over a 100 system. this helped. Thanks a ton for the same.
    – Rohit
    Nov 21, 2018 at 11:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.