Sql-server – The operation cannot be started by an account that uses SQL Server Authentication. SSIS Package
sql serverssis
I have a remote access in SSMS, when I try to execute an SSIS package I get, the operation can not be started by an account that uses SQL Server Authentication
How to fixed it?
Note: I can not use Windows Authentication remotely.
Best Answer
You cannot use a SQL Account to run an SSIS package in the Integration Services Catalog. There is logic within the CLR methods that are used to run the SSIS packages that reject non-windows authentication (as you have discovered).
As a consultant, I ran into issues where I'd use my corporate laptop plugged into a client's network. In that case, my account sdc\billinkc would not exist in the client domain. Instead, their servers would expect client\bfellows credentials to be presented. The work around is to use RunAs. Alternate reference
I have one for a command prompt (above), Visual Studio, SSMS, PowerShell, PowerShell ISE and some other specialty apps that need to work with their domain.
Using the runas approach, I've been able to deploy packages using all of the above methods (as well as deploying directly from a Visual Studio instance being run with foreign credentials).
You'd want to use ssms.exe and the correct install path to launch management studio and then SSIS packages would run.
Approach #2
If you don't have an account in the foreign domain and the only thing they are willing to do is create a sql login, then they will also need to
Create a Credential
Create a Proxy
Create a SQL Agent job that uses the proxy to run the SSIS package
Grant the sql login the rights to run the job
This will allow you to run an SSIS package in a manner that is allowed as well as present domain credentials to the required resources.
My personal opinion is that option #1 is the way to go. But I see no need for you to have to grant the domain account local administrator access. It seems to me that it requires access to certain folders and files and so you could grant the domain user access to only the resources that it needs to run the package successfully. This can be done through the file/folder properties dialog box and select the security tab - there should be no need to set it for every file and folder as you could set the permissions of the parent directory and set them to override child properties.
Two thoughts, both revolving around Impersonation:
From the description of the issue as well as the error messages, they all deal with errors related to attempts to Impersonate / EXECUTE AS. This attempt to switch the execution context could be the entire problem. Why are you using EXECUTE AS LOGIN = N'CrossDbCertLogin'? That login (and the associated certificate, etc.) exists as a proxy to get implied permissions, not to execute anything directly. If you look more closely at the example you followed from sqlxdetails.com, you will notice that there is never a call to EXECUTE AS LOGIN = N'HighPrivCertLogin'.
Hence, I would start by:
No more attempts to EXECUTE AS LOGIN = N'CrossDbCertLogin'
Your stored procedure that calls the three SSIS Catalog Procedures should either have noEXECUTE AS clause orEXECUTE AS CALLER (which is the default if not specified)
Grant execute permission on each SSIS stored procedure to the certificate-based user in the [SSISDB] database. These are the implied permissions that your restricted login will pick up from the link between the proc being signed with the certificate that maps to a user that does have the necessary permissions:
USE [SSISDB];
GRANT EXECUTE ON [Create_Execution] TO [CrossDbCertUser];
GRANT EXECUTE ON [Set_Execution_Parameter_Value] TO [CrossDbCertUser];
GRANT EXECUTE ON [Start_Execution] TO [CrossDbCertUser];
Of course, your example code doesn't show the creation of a local database user in either [msdb] or [SSISDB], but I assume these users were created. If not, then at the very least you need to create the user in [SSISDB] since that is where the database-level permission is needed (i.e. to execute the 3 SSIS procs). But it probably couldn't hurt to also create the certificate-based user in [msdb] as well.
If, for some reason (maybe SSIS, being an external process, cannot use an impersonated authentication token?) the execution context itself needs to be both privileged and impersonatable (yes, that is a perfectly cromulent word ;-), then the current approach won't work as you are trying to:
impersonate logins that cannot have an execution context (i.e. certificate- and asymmetric key- based logins)
impersonate via the EXECUTE AS clause of the CREATE PROCEDURE clause which cannot be reverted (I don't know why anyone would want to revert from that, but it is an error message you are getting when attempting this).
So, here are two things to try (again, assuming option # 1 above doesn't do the trick):
Create a SQLCLR stored procedure that does nothing more than connect via the connection string of "trusted_connection = true;" (which will connect to the default instance, presumably the instance you are currently on) and execute your stored procedure in [msdb] that runs the three SSIS procedures. This would work because unless you explicitly code for using Impersonation, it will by default make the external connection as the Windows/Active Directory account that is running the SQL Server process (i.e. the "Log On As" account in Services) and that account should be able to EXEC those SSIS procedures. This option wouldn't require any certificates or logins because you would GRANT EXECUTE on this SQLCLR stored procedure to that restricted login and this stored procedure makes a new, independent connection as a privileged login. This SQLCLR procedure has a single purpose such that logging in as the privileged login doesn't pose a security threat. The other key piece is: due to the connection being made by non-impersonated context, the authentication token can be passed to an external machine. But yes, the Assembly will need a PERMISSION_SET of EXTERNAL_ACCESS, and that should be accomplished by signing the Assembly and then creating an asymmetric key from the assembly and then creating a login based on that asymmetric key and then granting the EXTERNAL_ACCESS ASSEMBLY permission to that login.
If assistance is needed with the database side of the Assembly / Asymmetric Key steps, I wrote an article, Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies) (free registration required), that contains a step-by-step example of doing this; it just doesn't show the creation of the private key in Visual Studio.
or
Create a queue table that your restricted login has permissions to INSERT into. Then create a SQL Agent Job that checks the queue table and if a "New" record is found, updates the status to "In Process", calls the stored procedure in [msdb] that calls the three SSIS procedures, and then finished, updates the queue record again to "Completed". In this method SQL Agent is running the stored procedure via a new connection from a non-impersonated context; hence, the authentication token can be passed to an external machine.
Best Answer
You cannot use a SQL Account to run an SSIS package in the Integration Services Catalog. There is logic within the CLR methods that are used to run the SSIS packages that reject non-windows authentication (as you have discovered).
As a consultant, I ran into issues where I'd use my corporate laptop plugged into a client's network. In that case, my account sdc\billinkc would not exist in the client domain. Instead, their servers would expect client\bfellows credentials to be presented. The work around is to use RunAs. Alternate reference
From my answer on deploying ispac to foreign domain...
You'd want to use ssms.exe and the correct install path to launch management studio and then SSIS packages would run.
Approach #2
If you don't have an account in the foreign domain and the only thing they are willing to do is create a sql login, then they will also need to
This will allow you to run an SSIS package in a manner that is allowed as well as present domain credentials to the required resources.