Skip to main content
Solved

Connecting to SQL Server when runnning as a scheduled task

  • January 25, 2018
  • 7 replies
  • 339 views
tim_wood
Contributor
Forum|alt.badge.img+8

I have a Workspace running in FME Desktop 2017.1.1.1 on a server. The Workspace has a SQL Server Spatial Reader in it. The SQL Server database is on a different server which is in the same domain. The Reader is using a named SQL Server connection which is publicly visible. The connection uses SQL Server credentials to connect to the database (not Windows authentication). The connection is not encrypted.

The Workspace runs fine when I'm logged in. It works as a scheduled task when running as me. But when I try to run it as a scheduled task using a different user (we have a Domain service account for running scheduled tasks), it fails. I've tried the registry fix suggested here:

https://knowledge.safe.com/articles/32356/scheduling-software-doesnt-recognize-fme-connectio.html

However I still get "Invalid authorization specification" in the log file. Interestingly, since applying the above fix, I also get errors about encryption (see below). I don't know if this is progress or not!

One thing I've noticed from the log info below is that there is no "User ID" in the connection string. There's definitely one in the named connection in FME Options.

Any ideas?

2018-01-25 08:00:14| 0.6| 0.0|INFORM|FME Configuration: Using FME Reprojection Engine

 

2018-01-25 08:00:16| 2.8| 2.2|INFORM|Error: COM.safe.fmewebservices.FMEWebServiceException: COM.safe.fmewebservices.FMEWebServiceException: COM.safe.fmeserver.api.FMEServerException: User encryption is specified but can't find key to decrypt.

at COM.safe.fmewebservices.FMENamedConnection.getKeyValues(FMENamedConnection.java:231)

Caused by: COM.safe.fmewebservices.FMEWebServiceException: COM.safe.fmeserver.api.FMEServerException: User encryption is specified but can't find key to decrypt.

at COM.safe.fmewebservices.FMENamedConnectionUtils.AESdecryptData(FMENamedConnectionUtils.java:109)

at COM.safe.fmewebservices.FMENamedConnection.getKeyValues(FMENamedConnection.java:224)

Caused by: COM.safe.fmeserver.api.FMEServerException: COM.safe.fmeserver.api.FMEServerException: User encryption is specified but can't find key to decrypt.

at COM.safe.fmeserver.encryption.FMEEncryptionUtils.AESdecryptData(FMEEncryptionUtils.java:192)

at COM.safe.fmeserver.encryption.FMEEncryptionUtils.AESdecryptData(FMEEncryptionUtils.java:63)

at COM.safe.fmewebservices.FMENamedConnectionUtils.AESdecryptData(FMENamedConnectionUtils.java:105)

... 1 more

Caused by: COM.safe.fmeserver.api.FMEServerException: User encryption is specified but can't find key to decrypt.

at COM.safe.fmeserver.encryption.FMEEncryptionUtils.AESdecryptData(FMEEncryptionUtils.java:179)

... 3 more

 

2018-01-25 08:00:16| 2.8| 0.0|ERROR |Error: COM.safe.fmeserver.api.FMEServerException: User encryption is specified but can't find key to decrypt.

 

2018-01-25 08:00:16| 2.8| 0.0|INFORM|Creating reader for format: Microsoft SQL Server Spatial

 

2018-01-25 08:00:16| 2.8| 0.1|INFORM|Trying to find a DYNAMIC plugin for reader named `MSSQL_SPATIAL'

 

2018-01-25 08:00:16| 2.8| 0.0|INFORM|Loaded module 'MSSQL_SPATIAL' from file 'D:\\GIS\\FME\\FME_2017_1_1_1_64bit\\plugins/FME_ADO.dll'

 

2018-01-25 08:00:16| 2.8| 0.0|INFORM|FME API version of module 'MSSQL_SPATIAL' matches current internal version (3.8 20170315)

 

2018-01-25 08:00:16| 2.8| 0.0|INFORM|Microsoft SQL Server Spatial Reader: Read 1 DEF line(s). Found 5 attribute(s)

 

2018-01-25 08:00:16| 2.8| 0.0|INFORM|Microsoft SQL Server Spatial Reader: Opening `[db connection name]' for read operation

 

2018-01-25 08:00:16| 2.9| 0.0|INFORM|Microsoft SQL Server Spatial Reader: Trying to connect using SQL Server 2012 Native Client...

 

2018-01-25 08:00:16| 3.1| 0.3|ERROR |Microsoft SQL Server Spatial Reader: Failed to connect using SQL Server 2012 Native Client. Provider error '(-2147217843) Invalid authorization specification'. Connection string 'Provider=SQLNCLI11;DataTypeCompatibility=80;Data Source=;Initial Catalog=[db connection name];User ID=;Password=********'

 

2018-01-25 08:00:16| 3.1| 0.0|INFORM|Microsoft SQL Server Spatial Reader: Trying to connect using SQL Server 2008 Native Client...

 

2018-01-25 08:00:16| 3.1| 0.0|ERROR |Microsoft SQL Server Spatial Reader: Failed to connect using SQL Server 2008 Native Client. Provider error '(-2146824582) Provider cannot be found. It may not be properly installed.'. Connection string 'Provider=SQLNCLI10;DataTypeCompatibility=80;Data Source=;Initial Catalog=[db connection name];User ID=;Password=********'

 

2018-01-25 08:00:16| 3.1| 0.0|ERROR |Failed to open reader

Best answer by tim_wood

I discovered that the cause of my problem was that the public key file was in my profile (thus inaccessible to the scheduled task user). Moving fme_publicKey.jceks to a shared folder AND updating the public key path in FME (Tools > FME Options > Default Paths) fixed the problem. I have also added this info as a comment on the 2 articles about database connections.

View original
Did this help you find an answer to your question?

7 replies

david_r
Evangelist
  • david_rEvangelist
  • January 25, 2018

I'm guessing you've already tried the steps in this article?

https://knowledge.safe.com/articles/34678/making-database-and-web-connections-public.html

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • January 25, 2018
david_r wrote:

I'm guessing you've already tried the steps in this article?

https://knowledge.safe.com/articles/34678/making-database-and-web-connections-public.html

Yes. Connections are public and "fme_connections.data" is stored in a shared folder, not my profile. After the registry update, a file called "fme_publicKey.jceks" has appeared in the same folder as "fme_connections.data". I'm guessing this might be something to do with the SQL Server JDBC Reader but I'm not using that one.

 

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • January 25, 2018
david_r wrote:

I'm guessing you've already tried the steps in this article?

https://knowledge.safe.com/articles/34678/making-database-and-web-connections-public.html

The Domain account we use to run scheduled tasks is a member of the Administrators group on the server the task is running on and FME is installed on. So there should be no issue with accessing the folder the FME connection data is stored in.

 

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • October 12, 2018

I've hit this problem again. I don't think I got a resolution last time as I would have posted it here. Any further suggestions? How can I find out if it's an FME issue or something else like a Group Policy restriction?

Update: I can see no errors or warnings in Event Viewer at the time the task ran.

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • October 12, 2018
tim_wood wrote:

I've hit this problem again. I don't think I got a resolution last time as I would have posted it here. Any further suggestions? How can I find out if it's an FME issue or something else like a Group Policy restriction?

Update: I can see no errors or warnings in Event Viewer at the time the task ran.

OK. There may be something related to different versions of FME being installed to different folders (some in Z:\\GIS\\FME then by version and some in Z:\\FME then by version) and 2 database connection files existing. I applied the registry fix and set the path to one set of connection files (i.e. fme_connections.data and fme_publicKey.jceks) and have now changed it to the other. I will uninstall my old versions of FME and re-install them to the same location as the others and ensure they all use the same set of FME connection files.

 

 

However, I also noticed that in FME 2017.1, SQL Server connections added in 2018.1.0.1 appear incorrectly. What was created with a Server value of "server.domain\\instance" shows as "server.domain<backslash>instance" and therefore the connection doesn't work.

 

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • Best Answer
  • October 23, 2018

I discovered that the cause of my problem was that the public key file was in my profile (thus inaccessible to the scheduled task user). Moving fme_publicKey.jceks to a shared folder AND updating the public key path in FME (Tools > FME Options > Default Paths) fixed the problem. I have also added this info as a comment on the 2 articles about database connections.

D
  • denisb
  • April 18, 2019
tim_wood wrote:

I discovered that the cause of my problem was that the public key file was in my profile (thus inaccessible to the scheduled task user). Moving fme_publicKey.jceks to a shared folder AND updating the public key path in FME (Tools > FME Options > Default Paths) fixed the problem. I have also added this info as a comment on the 2 articles about database connections.

I had the same problem, but this hint worked for me.

The scheduled-Tasks user needs the UNC-Path (\\\\server\\..) an could not map the disk path (G:\\..).

Reply


Helpful Members This Week

Community Stats

31,031
Posts
117,602
Replies
38,810
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.