I have a very large Dynamics GP customer who has a very large and complex IT environment with a lot of security. In their Test Dynamics GP environment, they have the unusual situation where they have a firewall between their Test SQL Server, and their Test Dynamics GP application server.
To make things more complex, they have multiple SQL Server installations and instances on their Test SQL Server. This, combined with the firewall that is blocking UDP traffic, prevents them from using standard SQL Server instance names and dynamic TCP ports. In their Test environment, they have been using this naming convention with GP to connect to their Test SQL Server instance through the firewall:
SERVERNAME\INSTANCENAME,portnumber
SQL Server takes the unusual approach of using a comma to specify a port number, and I admit that this is the first time I had ever seen this connection string format that included the SQL Server port number.
For example:
globalhqsqlservertest7\instance123,49156
This allows them to connect to the SQL Server instance on port 49156.
Except when that doesn't work.
This server/instance,port format works with SQL Server Management Studio and also works fine with ODBC DSNs, allowing Dynamics GP to work. (BTW, they indicated that Management Reporter is unable to use the connection string with the port number)
However, what we discovered is that a Dynamics GP integration will not work when the connection string contains a comma. You will get SQL Connection Error 26:
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)No matter what we tried, we couldn't get a connection with the .NET Dynamics GP integration, and only after creating a test SQL connection app was I able to identify that GPConnNet was the culprit, and not a network or firewall issue.
My guess is that GPConnNet is either stripping out the comma/port, or is unable to connect using the comma/port connection string. A native SQL ADO.NET connection works fine using SQL authentication, but if you have a Dynamics GP related application that relies on GP username and password, you're stuck with GPConnNet.
After fruitlessly testing numerous workarounds, I found a discussion about SQL Server network Aliases. I've never had to use them previously, but they offer a way to assign a simple name to a SQL Server instance name, including a specific port number.
SQL Aliases are setup in the infrequently used SQL Server Configuration Manager. If you aren't familiar with the Configuration Manager application, I highly recommend understanding its role and capabilities. It is a critical tool for troubleshooting SQL Server connectivity issues.
In Configuration Manager, you should see a 32-bit driver and 64-bit driver. You will want to work with the one that matches your application. If you have your .NET app compiled to target x64, you'll use the 64-bit Alias, and vice versa for 32-bit apps.
After you save the Alias, it should start working immediately without restarting the SQL Server service.
With the 32-bit Alias setup, my test application, using GPConnNet, was finally able to connect to the SQL Server instance on port 49156.
So in the highly unlikely situation where you have to use GPConnNet with a SQL port number, there is a solution!
Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics
GP Certified IT Professional in Los Angeles. He is the owner of Precipio
Services, which provides Dynamics GP integrations, customizations, and
automation solutions.
1 comment:
Excellent finding!
Post a Comment