SDE Connection files and FME Server

I'm curious to know what others are doing for their SDE Connection file management when working with FME Server.

From our perspective...

'Publishing' a Connection File

When dealing with SDE Connections it is important to consider the pros and cons of where the SDE Connection files are stored.

When publishing a workspace to FME Server that makes use of an SDE Connection file, FME Workbench will automatically have it checked to be uploaded with the Workspace. Using the default means FME will place the connection file in the same folder (repository) as the workbench. Pro: This is a simple approach. Con: When a password changes the workspace will need to be republish. Since the SDE Connection file is in the repository with the FME workspace... any workspaces using that user (with the changed password) will also need to be republished. Not a huge deal if you only have a couple of workspaces using connection files, but what if there are dozens? Who do you call?

If you make heavy use of SDE Connection files you could go 1 of 2 ways to make your life easier.

1. Use a File Server: Place your SDE Connection files on a File Server accessible by UNC, otherwise known as a network share. In the FME workspace reference this network share location (using UNC ex. //<myfileserver>/myshare/sdeconnectionfiles) and select the appropriate SDE Connection file. When publishing the workspace to FME Server uncheck to upload the connection file. FME will use the connection file from this network share location. Pro: One place to manage your SDE Connection files, for all your applications. Con: If someone changes something in an SDE Connection file it could cause your FME Server workspace to fail. Who do you call?
2. Use FME Server Shared Resource: When publishing your workspace that references an SDE Connection file you have an option to select one of FME Server's Shared Resources folders to upload the connection file to. This then means the file is persistent on the server and can be referenced by other workspaces that are published to FME Server. Pro: You can see these files when publishing from FME Workbench and you can actually confirm their existence via the FME Server WebUI. You can upload and replace the connection files when needed (ensuring the same name is kept as referenced in existing workspaces). Con: Duplication of connection files. At this time these connection files would be copies from some other network location or someones desktop. So there is still a need to manage the connection files on the FME Server System Share (ex. replacing the connection files when passwords are change).

Security

Another consideration for the connection files is security. Who gets access to a Read/Write connection file - a stored database user with Update or Delete permissions... perhaps that is a concern and so you may want to rethink how you will store the connection files on FME Server by creating a new FME Server System Share folder for that purpose, limiting access.

Summary

So there you have some ideas to keep in mind when working with SDE Connection files and FME Server. It may make the most sense to use UNC paths, but then you are gambling that no one will ever change them. If this can't be achieved - perhaps using the FME Server System Share is the way to go, limiting the chances of other applications from interacting with the connection files used by FME workspaces.

We are curious what others are doing, so please share your thoughts. It may help us make the management of SDE Connection files better in the future.

Personally I usually publish the connection file without username/password as a shared resource on FME Server. That way the connection file is pretty much static.

I also publish a small config file as a shared resource, typically an ini-file because they are easy to read both by humans and by scripts. In my workspaces I have a couple of Python scripted parameters to extract the username and password from the ini-file which I use for the "Override credentials" setting of the ArcSDE reader.

Works nicely, but you should be a bit careful about putting database passwords in plain-text files unless you have a firm grasp on who has access to it.

You have the ability to create database connections in FME Server, why is one of the options not SDE connections? Can they not be managed this way?

I might be OK with read only ArcSDE connection files being in a shared area, but ones with write access need to be restricted. So new system share may be the answer.

If I have a Workspace with a read only ArcSDE connection and a write ArcSDE connection, I can't seem to choose separate locations on server for each one - they both have to go in the same place (that's using FME 2018). So it looks like I will have to create a Workspace with the read only connection file and publish that to upload it to the shared area, then use that in Workspace with the write connection file stored locally.

Is there no way to load ArcSDE connection files into the shared area outside of Workspace publishing? Can you just copy the files to the appropriate folder on the FME Server server?

Hi @tim_wood,

@jovitaatsafe done :-)

https://knowledge.safe.com/questions/109274/securing-arcsde-connection-files-on-fme-server.html