December 16, 2021 at 1:08 am
I used SQL Server Import and Export Wizard creating a SSIS Package, when creating the SSIS package, for the data source and destination source I used the SQL Server account with administrative privilege to access database; but when I create a job to execute this SSIS package, but shows the error as below, how can I handle it or is there a post for example ? thanks!
Error Message:
Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.7001.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:16:40 AM Could not load package "\MSDB\Test" because of error 0xC00160AE. Description: Connecting to the Integration Services service on the computer "DB01" failed with the following error: "Access is denied." By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service. Source: Started: 9:16:40 AM Finished: 9:16:40 AM Elapsed: 0.015 seconds. The package could not be loaded. The step failed.
December 16, 2021 at 4:07 pm
A few ways I can think of to fix this. I think the BEST solution to fix it is to change the SQL Server Agent service to be an AD account that has admin permissions on the server. The NEXT best solution would be to use a proxy account and the last suggestion I would have is to grant NT Service/SQLSERVERAGENT admin access on the server hosting the SSIS instance (if possible).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 16, 2021 at 6:28 pm
I would recommend the proxy account as the best solution. If you modify SQL Server Agent to run as a domain account (actually, that should be setup and configured with a gMSA), you then have to grant permissions to that account in SQL Server.
Using a proxy account, you would be setting the same permissions as would be needed for the service account.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 18, 2021 at 7:43 am
thanks Mr. Brian Gale and Jeffrey Williams!
I tried to use proxy account, the setting as the pictures, when I created a job and executed it, the error message as below:
Executed as user: bit\pqScott. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:06:54 Error: 2021-12-18 11:06:55.01 Code: 0xC002F210 Source: Preparation SQL Task 1 Execute SQL Task Description: Executing the query "CREATE TABLE [dbo].[SaleTable] ( [Origin..." failed with the following error: "CREATE TABLE permission denied in database 'RRDB01'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:06:54 Finished: 11:06:55 Elapsed: 0.64 seconds. The package execution failed. The step failed.
December 18, 2021 at 1:11 pm
error is very clear - that user (bit\pqScott) does not have permissions to create the table on database RRDB01 - fix the permissions as required.
December 18, 2021 at 4:14 pm
error is very clear - that user (bit\pqScott) does not have permissions to create the table on database RRDB01 - fix the permissions as required.
actually, the user ( bit\pqscott) has sa permission on database RRDB01 (destination ). the user ( bit\pqscott) was added into source database and destination database, and ( bit\pqscott) wast granted sa permission, so I don't know how to fix it, thanks!
December 18, 2021 at 4:26 pm
Looking at your pictures - you are not using the Integration Services Catalog. I would recommend that you setup and configure the catalog and modify your packages to project deployment and deploy to the catalog.
Either way - the error message is clear. The user connecting to the instance where it is attempting to create that table does not have permissions to create the table. If that connection is using windows authentication, it is the proxy account that doesn't have permissions. If the connection is a SQL account - that SQL account doesn't have permissions.
Those permissions are required on the system where that connection is actually connected - not where the SSIS package is saved/stored. My guess is that you have this package on one server - and the package connects to another server as the destination.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply