Skip to main content
  • EN

My workspace uses a Microsoft SQL Server Spatial reader to extract its source data and all has been working fine until told my source database is moving to a new server. Fortunately I still have access to both existing and the new one to migrate and confirm the db connection is working. The server name I was given is of the format: server\\instance,port (eg. SVRxxxx\\INSxxxxx,51433). I am using Windows authentication and have confirmed my credentials using Sql Server Mgmt Studio. When I attempt to Add a database connection in FME options, I enter the database name into the Server field of the Connection Parameters in exactly the same format as I did in SSMS (and which I had always done successfully in the past when my source db did not have a comma port number in the server name), but when I press the ellipsis to obtain the list of databases to which I can connect, I receive an error: Connection failed. Connection string `Provider=SQLNCLI11;DataTypeCompatibility=80;Data Source=WCxxxxxxxx01A\\PxxD,51433;Integrated Security=SSPI'. Provider error `(-2147467259) TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.'

My suspicion is the comma and port number in the server name might be tripping up FME. Everything was going along fine until that came along. When looking behind the scenes at SQL Server, comparing the DB that was working fine and the new one I am supposed to now connect to, everything appears to be about the same. The version of SQL Server has changed slightly 12.0.4100.1 to 12.0.4436.0, and like I mentioned SSMS connects just fine to the new server name. Remote connections are enabled. My FME Desktop is 2018.1.0.2 (20180903 - Build 18547 - Win32). Screen shot and error log attached. I've redacted server names, but hopefully not too obscured to see what's going on. Any ideas are appreciated as I am pretty much stuck at this point.

Attachments:

FMEConnErr.pngFME_ConnectionLogError.txt

@gis3780 Could you give me an example of the connection string you use for the original (working) connection? Does it include an instance name?

@gis3780 Could you give me an example of the connection string you use for the original (working) connection? Does it include an instance name?

@MarkAtSafe In the working connection, I am not specifying an instance name or a port number, just the db server name. I have attached a screen shot to show what the db connection dialog looks like. Hopefully I didn't obscure too much to see what's going on. I was able to confirm with a dba that the default port 1433 has been the standard until a recent policy change was enacted such that all connections must be made through a non-default port number, hence the comma port number in the connection string. And then on top of that, when our db was just moved to a new server it was under a named instance as well. Thanks for your response. Let me know if I can clarify anything further.

@MarkAtSafe Sorry to take your time thinking about this, but FME appears to be fine with the revised connection string format to include the port number: server\\instance,port. It turns out there was yet another dba policy instituted unbeknownst to me whereby certain machines were being blocked from connecting to the new server, including the one on which I was attempting to establish the updated db connection in FME Desktop. When I moved Desktop to a more privileged machine, the connection worked fine. Thanks again for your response, and sorry to take your time, but thought you'd also be happy to know that all is fine with the FME part of this issue.

@gis3780 Could you give me an example of the connection string you use for the original (working) connection? Does it include an instance name?

@MarkAtSafe Sorry to take your time thinking about this, but FME appears to be fine with the revised connection string format to include the port number: server\\instance,port. It turns out there was yet another dba policy instituted unbeknownst to me whereby certain machines were being blocked from connecting to the new server, including the one on which I was attempting to establish the updated db connection in FME Desktop. When I moved Desktop to a more privileged machine, the connection worked fine. Thanks again for your response, and sorry to take your time, but thought you'd also be happy to know that all is fine with the FME part of this issue.

@MarkAtSafe Sorry to take your time thinking about this, but FME appears to be fine with the revised connection string format to include the port number: server\\instance,port. It turns out there was yet another dba policy instituted unbeknownst to me whereby certain machines were being blocked from connecting to the new server, including the one on which I was attempting to establish the updated db connection in FME Desktop. When I moved Desktop to a more privileged machine, the connection worked fine. Thanks again for your response, and sorry to take your time, but thought you'd also be happy to know that all is fine with the FME part of this issue.

@gis3780 Thanks for letting us know you have found the solution to your connection issue.

Reply


Community Stats

30,109
Posts
114,295
Replies
37,744
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.