Question

Use SQL Executor to update microsoft server table

P
Badge +1

Within a fme workspace, i am using the SQL Executor transformer to update a database table using the following SQL script: 

UPDATE [GIS].[dbo].[cadastreNSW_ProposedPlan]

SET authority = 1      
FROM [GIS].[dbo].[cadastreNSW_ProposedPlan] AS plans
INNER JOIN
(
    SELECT DISTINCT DP,pcl_flg
    FROM [GIS].[dbo].[vAUTHLot]
) AS authority

ON REPLACE(REPLACE(plans.ppn, 'D', ''), 'P', '') = authority.DP

WHERE plans.authority != 2 AND authority.pcl_flg = 'P'

 

This SQL script uses an embedded database connection.  When I execute the FME workspace the SQL Executor runs with no problems, updating the table.  However, when other users run the FME workspace the get the following error in the log file:

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=GIS;User ID=;Password=********'

 

My question is:  If the workspace is using an embedded database connection, why do we get this user dependent error and how do we solve it?

7 replies

F
Badge

@philipwhitten This looks to be a permission issue as you can clearly run the workspace but you colleague cannot connect using the embedded connection credentials.

Can you try and use SQL Server Management Studio on the profile of the user (who is trying to run the workspace) using the embedded credentials details?

Just out of curiosity, is your workbench reading any other data sources before it hits the SQL Executor? Does the user have access to these data sources?

P
Badge +1

@philipwhitten This looks to be a permission issue as you can clearly run the workspace but you colleague cannot connect using the embedded connection credentials.

Can you try and use SQL Server Management Studio on the profile of the user (who is trying to run the workspace) using the embedded credentials details?

Just out of curiosity, is your workbench reading any other data sources before it hits the SQL Executor? Does the user have access to these data sources?

The workbench uses a Microsoft SQL Reader with the same embedded connection to initiate the SQL Executor. The user is successful using this SQL reader. What is strange is that the permission issue allows the user to successfully use the SQL reader, but, throws an error when for the SQL Executor using the same embedded connection.

M
Badge +2

@philipwhitten It may also depend on whether you selected Windows or SQl Server Authentication. If you selected Windows Authentication then those users need access to the database as @bombaypj suggests.

P
Badge +1

@philipwhitten It may also depend on whether you selected Windows or SQl Server Authentication. If you selected Windows Authentication then those users need access to the database as @bombaypj suggests.

I have used an Embedded Connection (SQL Server Authentification). I don't observe the permission problem when I use Readers or Writers with a SQL Server Authentification. I only observe the permission problem with the SQL Executor transformer.

M
Badge +2

I have used an Embedded Connection (SQL Server Authentification). I don't observe the permission problem when I use Readers or Writers with a SQL Server Authentification. I only observe the permission problem with the SQL Executor transformer.

@philipwhitten SQL Server has different permissions for INSERT UPDATE SELECT (https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-2017 ) so perhaps your user does not have sufficient permissions for the updates but can read or insert data.

P
Badge +1

@philipwhitten SQL Server has different permissions for INSERT UPDATE SELECT (https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-2017 ) so perhaps your user does not have sufficient permissions for the updates but can read or insert data.

Hello Markatsafe. Why would it matter what my user's permissions are when we are using a SQL Server Authentification?

M
Badge +2

Hello Markatsafe. Why would it matter what my user's permissions are when we are using a SQL Server Authentification?

@philipwhitten I'm afraid we can't reproduce the issue here. This definitely looks like a permissions issue for the database. But to resolve that I think you should contact your FME reseller or file an support case through safe.com/support f you purchase FME directly from Safe.

Reply


Community Stats

28,666
Posts
109,343
Replies
36,535
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.