February 19, 2012 at 6:40 am
Hi all,
I made SSIS package on development server using 2 connections:
- Flat File Source : Read/write access on a shared network drive
- ADO.NET Source : INSERT/DELETE access to a table of the SQL Server Database using Windows Integrated Security.
I am sysadmin of the database and I have access of the network drive so the package runs fine.
Now i want to deploy and run(using Job) it on our production server using a proxy account to run but i have never used a proxy account.
I'd like to give a minimum access to this account.
Which minimum rights do I need to grant to this domain user ?
Of course this user need to access to the network drive but for SQL Server ...
Thanks in advance.
Jean-yves
February 19, 2012 at 7:34 am
February 20, 2012 at 2:19 am
jeanyvesR (2/19/2012)
Hi all,I made SSIS package on development server using 2 connections:
- Flat File Source : Read/write access on a shared network drive
- ADO.NET Source : INSERT/DELETE access to a table of the SQL Server Database using Windows Integrated Security.
I am sysadmin of the database and I have access of the network drive so the package runs fine.
Now i want to deploy and run(using Job) it on our production server using a proxy account to run but i have never used a proxy account.
I'd like to give a minimum access to this account.
Which minimum rights do I need to grant to this domain user ?
Of course this user need to access to the network drive but for SQL Server ...
Thanks in advance.
Jean-yves
1. got to tab ....Security> Credentials, you need to create a credential that having rights to access your SQL server...
2. SQL Server Agent>Proxies, you need to create a proxy account by giving the credential name that you have created in the previous step and choose the SQL server Integration Service Package for the subsystem and then ok.
3. create a new job by giving the proxy account as owner.:-)
Thats it...
Thanks,
Charmer
February 20, 2012 at 3:04 am
Thanks Charmer,
Regarding SQL server , I need to clarify permissions :
1) Create login (CREATE LOGIN Domain\PROXY_USER FROM WINDOWS;)
2) Create User (USE PROD_DB;CREATE USER Domain\PROXY_USER FOR LOGIN Domain\PROXY_USER ; )
3) Grant permissions (GRANT SELECT,INSERT,DELETE ON [dbo].[T_TEST_TST] TO [Domain\PROXY_USER];)
Did I miss something ?
Jean-Yves
February 20, 2012 at 5:35 am
jeanyvesR (2/20/2012)
Thanks Charmer,Regarding SQL server , I need to clarify permissions :
1) Create login (CREATE LOGIN Domain\PROXY_USER FROM WINDOWS;)
2) Create User (USE PROD_DB;CREATE USER Domain\PROXY_USER FOR LOGIN Domain\PROXY_USER ; )
3) Grant permissions (GRANT SELECT,INSERT,DELETE ON [dbo].[T_TEST_TST] TO [Domain\PROXY_USER];)
Did I miss something ?
Jean-Yves
are you creating a user who is a member of system administrator...?
Thanks,
Charmer
February 20, 2012 at 5:50 am
No I want to give only required privilege for this user to run my SSIS package.
February 20, 2012 at 5:55 am
jeanyvesR (2/20/2012)
No I want to give only required privilege for this user to run my SSIS package.
yeah..that's enough then....:satisfied:
Thanks,
Charmer
February 20, 2012 at 6:09 am
Sorry :crying:, but I going to run the job and I got following error message :
Message
Executed as user: Domain\PROXY_USER. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 13:58:38 Could not load package "\MSDB\PAC_IMPORT_PO" because of error 0x80040E09. Description: The EXECUTE permission was denied on the object 'sp_ssis_getpackage', database 'msdb', schema 'dbo'. Source: Microsoft SQL Server Native Client 10.0 Started: 13:58:38 Finished: 13:58:38 Elapsed: 0.031 seconds. The package could not be loaded. The step failed.
It seems that some permission is missing ...
February 20, 2012 at 6:32 am
jeanyvesR (2/20/2012)
Sorry :crying:, but I going to run the job and I got following error message :Message
Executed as user: Domain\PROXY_USER. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 13:58:38 Could not load package "\MSDB\PAC_IMPORT_PO" because of error 0x80040E09. Description: The EXECUTE permission was denied on the object 'sp_ssis_getpackage', database 'msdb', schema 'dbo'. Source: Microsoft SQL Server Native Client 10.0 Started: 13:58:38 Finished: 13:58:38 Elapsed: 0.031 seconds. The package could not be loaded. The step failed.
It seems that some permission is missing ...
yeh..its a permission issue to access the MSDB...
ok...this may help you...just give a try...
Use MSDB
Go
GRANT EXECUTE ON dbo.sp_dts_getpackage to 'yourusername'
--try with no single quotes...
Thanks,
Charmer
February 20, 2012 at 2:16 pm
jeanyvesR (2/20/2012)
Sorry :crying:, but I going to run the job and I got following error message :Message
Executed as user: Domain\PROXY_USER. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 13:58:38 Could not load package "\MSDB\PAC_IMPORT_PO" because of error 0x80040E09. Description: The EXECUTE permission was denied on the object 'sp_ssis_getpackage', database 'msdb', schema 'dbo'. Source: Microsoft SQL Server Native Client 10.0 Started: 13:58:38 Finished: 13:58:38 Elapsed: 0.031 seconds. The package could not be loaded. The step failed.
It seems that some permission is missing ...
SQL Server maintains some SSIS related meta-data and configuration tables in the [MSDB] database, and your proxy account needs to access these. However, you don't need to explicitly grant permission to each of these tables. There are some builtin SQL Server roles for SSIS, and I think that granting your proxy account membership in DB_SSISOPERATOR will resolve your error.
Using Integration Services Roles
http://msdn.microsoft.com/en-us/library/ms141053.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 21, 2012 at 6:24 am
Many thanks Eric , I have added the credential in MSDB db_ssisoperator role and it works fine.
Before this try I have found this MS kb : http://support.microsoft.com/kb/912911/en-us and a note in paragrah Method 2 said :
The user who is specified in the credential must have the "Log on as a batch job" permission on the computer that is running SQL Server 2005
It's strange because I run the job without the "Log on as a batch job" permission set to the credential and it works .
February 21, 2012 at 7:55 am
jeanyvesR (2/21/2012)
Many thanks Eric , I have added the credential in MSDB db_ssisoperator role and it works fine.Before this try I have found this MS kb : http://support.microsoft.com/kb/912911/en-us and a note in paragrah Method 2 said :
The user who is specified in the credential must have the "Log on as a batch job" permission on the computer that is running SQL Server 2005
It's strange because I run the job without the "Log on as a batch job" permission set to the credential and it works .
I believe that membership in the server role db_ssisoperator is all that's needed for an account to "run" a SSIS package, and beyond that specific permissions will be needed on user objects, files/folders, etc. for the SSIS to perform it's tasks.
That reference to "Log on as a batch job" permission may apply to the service account you've designated for SQL Server Agent to run under. I think that some required roles and permissions are granted to service accounts when initially installing SQL Server.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 28, 2012 at 8:25 am
Look under MSDB at the roles db_dtsltduser,db_dtsltduser,db_dtsoperator, research and find the right one for you proxy.
Also dont forget to give reader on your config database, if you have one, or to any xml file configs.
You may also run into file locations on the C: drive of your server, for file buffers, caching, etc... SSIS like to play games regarding temporary storage, especially when running script components that reference DLL's, our proxy has access to C:\Windows\Temp and to user profile locations for the agent profile, all these odd locations were found by using Process Monitor and looking for Access Denied events.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply