SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

When you call the SSIS package from a SQL Server Agent job step, the SSIS package does not run. However, if you do not modify the SSIS package, it will run successfully outside SQL Server Agent.

It works if the package is created in the same environment with the same credential and scheduled it in a job with the same credential with a proxy account, but when working with multiple servers with different credential, some issues may arise.

For example:

• When the package developer use BIDS to create and save the package and another developer tries to run the package, it will throw an error “Login Failed for the user “
• If the package developer deploys the package to the production and when schedule it to execute through SQL server agent job, the job will fail.

Reason:

• The current user cannot decrypt secrets from the package. This scenario can occur if the current account or the execution account differs from the original package author, and the package's ProtectionLevel property setting does not let the current user decrypt secrets in the package.
• A SQL Server connection that uses integrated security fails because the current user does not have the required permissions.
• File access fails because the current user does not have the required permissions to write to the file share that the connection manager accesses.

Resolution:

Use a SQL Server proxy account:  The Proxy account must use credential that allows SQL server agent run the job as the account that has the required permissions. This method decrypts secrets and satisfies the key requirement by the user.

But the same will not work if we move the package to different computer even if we use the correct proxy account.

Set the SSIS Package Protection Level property to ServerStorage: Change the SSIS Package Protection Level property to ServerStorage. This setting stores the package in a SQL Server database and allows access control through SQL Server database roles. The one major drawback is that it will not work when deploying a package from a development environment in BIDS to another environment.

Use SSIS Package configuration files Use config file to store sensitive information, and then store this in a secured folder. You can then change the ProtectionLevel property to DontSaveSensitive so that the package is not encrypted and does not try to save secrets to the package. When you run the SSIS package in a job, the required information is loaded from the configuration file.  Ensure that the configuration files are protected as if they contain sensitive information. The disadvantage here is to maintain a configuration file in a secured folder and there may be chances of viewing the config file or getting login failed issue again.

So the best option that works well in development environment where several developers may want to edit the same package is to create the package with the connection manager on windows authentication account with least privilege or create a package template that uses a protection level that differs from the default setting. This problem will not occur in future packages.

Permission to execute the package:

db_ssisadmin	Enumerate own packages.	Import packages.
or	Enumerate all packages.	Delete own packages.
sysadmin	View own packages.	Delete all packages.
	View all packages.	Change own package roles.
	Execute own packages.	Change all package roles.
	Execute all packages.	Bitmap
	Export own packages.
	Export all packages.
	Execute all packages in SQL Server Agent.
db_ssisltduser	Enumerate own packages.	Import packages.
	Enumerate all packages.	Delete own packages.
	View own packages.	Change own package roles.
	Execute own packages.
	Export own packages.
db_ssisoperator	Enumerate all packages.	None
	View all packages.
	Execute all packages.
	Export all packages.
	Execute all packages in SQL Server Agent.
Windows administrators	View execution details of all running packages.	Stop all currently running packages

Transfer logins SQL task

Previously I have posted an article for transferring logins between instances using stored procedures sp_hexadecimal and sp_help_revlogin. Here in this article I am going to use another method to transfer logins between two instances.

Using BIDS you can transfer either all the logins or particular logins from desired database or all logins except sa from source server to destination server. But the downside is after the transfer, the sql logins are assigned some random passwords and  all the logins are disabled. You need to change the password and enable the login manually  before it can be used on the destination.

Steps to create an ssis package to transfer the  logins:

• Open an SSIS project
• Give a relevant name to the package and select the path to save the project
• Drag and drop the Transfer Login Task from toolbox to control flow area.
• Right click on the task and select edit
• Specify the source and destination server
• Choose the logins that you need to transfer.
• LoginsToTransfer - You have three options for selecting logins:
• AllLogins - This will transfer all logins from the source.
• SelectedLogins - This allows you to select specific logins
• AllLoginsFromSelectedDatabases - This allows you to transfer all logins associated with one or more databases as shown in the image below.
• LoginsList - This will allow you to select specific logins if you select SelectedLogins for LoginsToTransfer
• DatabaseList - This will allow you to select the databases if you select AllLoginsFromSelectedDatabases for LoginsToTransfer
• Choose the desired option in options section.
• IfObjectExists - If the logins are already exists on the destination you can select any of these 3 choices. First FailTask execution, second Overwrite the destination logins and third Skip the existing login and continue with the others.
• CopySids - if you set this option to True then security identifiers (SIDs)  will also transfer  to the destination

• Now execute the package and check the logins are transferred to the destination server.  Note: Only sysadmin can execute the package

Transfer logins and passwords between the instances of SQL Server 2005 and 2008

At times we need to replicate the same database from one server to another, after moving users may not be able to use the database and receive the error as

Login failed for user ‘TestUser’, (Microsoft SQL Server, Error: 18456)

This may be due to unavailable of logins and passwords on the instance to which the database has been moved. To transfer the logins and passwords from one instance to another follow the below steps,

·        Connect to the instance from where the database has moved.

·        Copy the below scripts into the query window and execute

USE master

GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

  DROP PROCEDURE sp_hexadecimal

GO

CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar (514) OUTPUT

AS

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

  DECLARE @tempint int

  DECLARE @firstint int

  DECLARE @secondint int

  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

  SELECT @firstint = FLOOR(@tempint/16)

  SELECT @secondint = @tempint - (@firstint*16)

  SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

  SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

  DROP PROCEDURE sp_help_revlogin

GO

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL

AS

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary  varbinary (256)

DECLARE @PWD_string  varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr  varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)

  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled,

p.default_database_name, l.hasaccess, l.denylogin

FROM sys.server_principals p LEFT JOIN sys.syslogins l

ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' )

AND p.name <> 'sa'

ELSE

  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled,

p.default_database_name, l.hasaccess, l.denylogin

FROM sys.server_principals p LEFT JOIN sys.syslogins l

ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' )

AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name,

@type, @is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

  PRINT 'No login(s) found.'

  CLOSE login_curs

  DEALLOCATE login_curs

  RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE())

+ ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

  IF (@@fetch_status <> -2)

  BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@type IN ( 'G', 'U'))

    BEGIN -- NT authenticated account/group

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name )

+ ' FROM WINDOWS WITH DEFAULT_DATABASE =

[' + @defaultdb + ']'

    END

    ELSE BEGIN -- SQL Server authentications

        -- obtain password and sid

   SET @PWD_varbinary = CAST( LOGINPROPERTY( @name,

                 'PasswordHash' ) AS varbinary (256) )

    EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state

    SELECT @is_policy_checked = CASE is_policy_checked

WHEN 1 THEN 'ON' WHEN 0

THEN 'OFF' ELSE NULL END FROM sys.sql_logins

WHERE name = @name

SELECT @is_expiration_checked = CASE is_expiration_checked

WHEN 1 THEN 'ON' WHEN 0

THEN 'OFF' ELSE NULL END FROM sys.sql_logins

WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN '

+ QUOTENAME( @name ) + '

WITH PASSWORD = ' + @PWD_string + ' HASHED,

SID = ' + @SID_string + ',

DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )

        BEGIN

   SET @tmpstr = @tmpstr + ', CHECK_POLICY = '

+ @is_policy_checked

        END

        IF ( @is_expiration_checked IS NOT NULL )

        BEGIN

   SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = '

+ @is_expiration_checked

        END

    END

    IF (@denylogin = 1)

    BEGIN -- login is denied access

      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO '

+ QUOTENAME( @name )

    END

    ELSE IF (@hasaccess = 0)

    BEGIN -- login exists but does not have access

      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO '

+ QUOTENAME( @name )

    END

    IF (@is_disabled = 1)

    BEGIN -- login is disabled

      SET @tmpstr = @tmpstr + '; ALTER LOGIN '

+ QUOTENAME( @name )

+ ' DISABLE'

    END

    PRINT @tmpstr

  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary,

@name, @type,

@is_disabled, @defaultdb, @hasaccess, @denylogin

   END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

·        The above script creates two stored procedures in the master database, they are sp_hexadecimal and the sp_help_revlogin stored procedures.

·         Once the stored procedure gets created, execute it in a new query editor window.

EXEC sp_help_revlogin

·        The output script that is generated by the sp_help_revlogin stored procedure is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

·        Start SQL Server Management Studio and then connect to the instance of SQL Server to which the database has moved

·        Open a new Query Editor window, and then run the output script that is generated. you can edit the script to avoid creating the redundant logins. Before run the script to the destination server read the below points.

Note:

·        Assess the output script cautiously. If server 1 and server 2 are in different domains, you will have to modify the output script and replace the original domain name with the new domain name in the CREATE LOGIN statements. The integrated logins that are granted access in the new domain do not have the same SID as the logins in the original domain. Therefore, the users are orphaned from these logins. To resolve the permission problem, refer this link, http://support.microsoft.com/kb/240872

·        The script does not transfer the default database information for a particular login, because the default database may not always exist on server 2. To define the default database for a login, use the ALTER LOGIN statement by passing in the login name and the default database as arguments.

·        The sort order of both the servers may be case sensitive, or the sort order of both the servers may be case insensitive. In these cases, the users do not experience a problem. If not, users will experience a problem.