Solved

Connecting to SQL Server when runnning as a scheduled task

  • 25 January 2018
  • 7 replies
  • 93 views
tim_wood
Rank
Badge +7

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
icon

Best answer by tim_wood 23 October 2018, 13:24

View original

7 replies

david_r
Rank
Userlevel 4

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
Rank
Badge +7

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
Rank
Badge +7

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
Rank
Badge +7

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
Rank
Badge +7

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
Rank
Badge +7

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

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


Community Stats

28,723
Posts
109,511
Replies
36,559
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.