SSIS: Password Policy Rules Not Met When Creating Catalog

Last week I was at a client site showing them how to set up the Integration Services Catalog. We were walking through the steps in how to create it and we input the password into the Create Catalog configuration screen. It was a strong password, that adhered to all domain requirements. However, when we clicked Ok, we received the following error:

TITLE: Microsoft SQL Server Management Studio

——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Management.IntegrationServices)

——————————

ADDITIONAL INFORMATION:

The transaction ended in the trigger. The batch has been aborted.

========================

   PASSWORD POLICY RULES NOT MET

========================

Login: “##MS_SSISServerCleanupJobLogin##” either has “Enforce password policy” check set to OFF

OR

has “Enforce password expiration” check set to ON.

[Correct Values: “Enforce password policy” = Checked; “Enforce password expiration”= Unchecked ]

Rolling back…

===============================================

(Microsoft SQL Server, Error: 3609)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3000&EvtSrc=MSSQLServer&EvtID=3609&LinkId=20476

——————————

BUTTONS:

OK

——————————

Ok, let’s check our password again. Type a stronger password, try it again. Still no luck. Going out to Bingoogle didn’t provide much assistance, apart from some old SQL Server 2000 issues with this from the database engine when creating a login. So that’s not it.

Let’s go check the local security policy and the domain policy. Yep, password meets requirements. Don’t see anything that’s terribly out of wack. So my next step was to go check the event viewer and application logs to look for an error, but there was nothing there. This server was created by another team off-site, so let’s go check the OS locale settings and the collation level for SQL Server to see if there are some settings off somewhere. still nothing! At this point I’m starting to sweat a little. In this environment, we’d created two servers (one for SSIS and the DB Engine and one for SSAS), so let’s switch over to the other server and see if it happens there too. yep, same problem. My next step was to create an inbound rule in the firewall settings, thinking that maybe there was some port blocking happening somewhere. A twitter out reach on #sqlhelp didn’t yield any results either. Although I do want to throw a quick thanks out to Robert Davis and Denny Cherry for the assistance!

When that still failed, I figured I’d just try and create the Catalog with Powershell. Fortunately, the code to do this is readily available on MSDN:

# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection

# Provision a new SSIS Catalog
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "P@assword1")
$catalog.Create()

I ran it locally on the server, and it failed with the same issue. There has to be something in the internal scripts causing this issue. So my next step was to go back to the basics. We do know that the issue is being caused by this specific login as a part of the process: “##MS_SSISServerCleanupJobLogin##”. Well, CleanupJobLogin sounds like the account that’s used to run the SSIS Server Maintenance Job. If we can get everything to create except for the job, that gives us the Catalog and the main SSISDB. All that login does is serve as the job owner for the “SSIS Server Maintenance Job”, which runs two procedures in the SSISDB: [internal].[cleanup_server_retention_window] and [internal].[cleanup_server_project_version].

Fire up Profiler, and run a trace. Go back in to “Create Catalog”, type the password, and click Ok. I know it’s going to fail, but that’s not what I’m after here. Go into the trace and capture all of the SQL scripts that the Catalog is running.

select SERVERPROPERTY(N’servername’)
DECLARE @edition sysname;
SET @edition = cast(SERVERPROPERTY(N’EDITION’) as sysname);
select case when @edition = N’SQL Azure’ then 2 else 1 end as ‘DatabaseEngineType’

exec sp_executesql N’SELECT
”IntegrationServices[@Name=” + quotename(CAST(SERVERPROPERTY(N”Servername”) AS sysname),””””) + ”]” AS [Urn],
CAST(SERVERPROPERTY(N”Servername”) AS sysname) AS [Name]
WHERE
(CAST(SERVERPROPERTY(N”Servername”) AS sysname)=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’PWIRTPTPSP3W12′

SELECT ISNULL(HAS_PERMS_BY_NAME(null, null, ‘ALTER SETTINGS’), 0)
SELECT ISNULL(IS_SRVROLEMEMBER (‘sysadmin’), 0)
GO
————————————————————–

USE [master]
select SERVERPROPERTY(N’servername’)
DECLARE @edition sysname;
SET @edition = cast(SERVERPROPERTY(N’EDITION’) as sysname); select case when @edition = N’SQL Azure’ then 2 else 1 end as ‘DatabaseEngineType’

exec sp_executesql N’SELECT
”IntegrationServices[@Name=” + quotename(CAST(SERVERPROPERTY(N”Servername”) AS sysname),””””) + ”]” AS [Urn],
CAST(SERVERPROPERTY(N”Servername”) AS sysname) AS [Name]
WHERE
(CAST(SERVERPROPERTY(N”Servername”) AS sysname)=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’PWIRTPTPSP3W12′
GO

———————————————————————————–

USE [master]
–exec sp_reset_connection
DECLARE @edition sysname;
SET @edition = cast(SERVERPROPERTY(N’EDITION’) as sysname); select case when @edition = N’SQL Azure’ then 2 else 1 end as ‘DatabaseEngineType’
SELECT
cfg.name AS [Name],
cfg.configuration_id AS [Number],
cfg.minimum AS [Minimum],
cfg.maximum AS [Maximum],
cfg.is_dynamic AS [Dynamic],
cfg.is_advanced AS [Advanced],
cfg.value AS [ConfigValue],
cfg.value_in_use AS [RunValue],
cfg.description AS [Description]
FROM
sys.configurations AS cfg

exec sp_executesql N’
        –Preparing to access the Catalog object
        DECLARE @t_catalogs TABLE (
        Name sysname COLLATE SQL_Latin1_General_CP1_CI_AS,
        EncryptionAlgorithm nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS,
        SchemaVersion int,
        SchemaBuild nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS,
        OperationLogRetentionTime int,
        MaxProjectVersions int,
        OperationCleanupEnabled bit,
        VersionCleanupEnabled bit,
        ServerLoggingLevel int,
        OperationLogNumberOfRecords int,
        VersionLogNumberOfRecords int)

        IF DB_ID(”SSISDB”) IS NOT NULL
        BEGIN
        INSERT INTO @t_catalogs VALUES(
        ”SSISDB”,
        (SELECT [property_value] FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”ENCRYPTION_ALGORITHM”),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”SCHEMA_VERSION”),
        (SELECT [property_value] FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”SCHEMA_BUILD”),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”RETENTION_WINDOW”),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”MAX_PROJECT_VERSIONS”),
        (SELECT CAST([property_value] AS BIT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”OPERATION_CLEANUP_ENABLED”),
        (SELECT CAST([property_value] AS BIT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”VERSION_CLEANUP_ENABLED”),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N”SERVER_LOGGING_LEVEL”),
        (SELECT COUNT(operation_id) FROM [SSISDB].[catalog].[operations]),
        (SELECT COUNT(object_id) FROM [SSISDB].[catalog].[object_versions])
        )
        END

SELECT
”IntegrationServices[@Name=” + quotename(CAST(SERVERPROPERTY(N”Servername”) AS sysname),””””) + ”]” + ”/Catalog[@Name=” + ”””” + REPLACE((SELECT Name from @t_catalogs), ””””, ””””””) + ”””” + ”]” AS [Urn],
(SELECT Name from @t_catalogs) AS [Name],
(SELECT EncryptionAlgorithm from @t_catalogs) AS [EncryptionAlgorithm],
(SELECT SchemaVersion from @t_catalogs) AS [SchemaVersion],
(SELECT SchemaBuild from @t_catalogs) AS [SchemaBuild],
(SELECT OperationLogRetentionTime from @t_catalogs) AS [OperationLogRetentionTime],
(SELECT MaxProjectVersions from @t_catalogs) AS [MaxProjectVersions],
(SELECT OperationCleanupEnabled from @t_catalogs) AS [OperationCleanupEnabled],
(SELECT VersionCleanupEnabled from @t_catalogs) AS [VersionCleanupEnabled],
(SELECT ServerLoggingLevel from @t_catalogs) AS [ServerLoggingLevel],
(SELECT OperationLogNumberOfRecords from @t_catalogs) AS [OperationLogNumberOfRecords],
(SELECT VersionLogNumberOfRecords from @t_catalogs) AS [VersionLogNumberOfRecords]
WHERE
(CAST(SERVERPROPERTY(N”Servername”) AS sysname)=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’PWIRTPTPSP3W12′

SELECT ISNULL(IS_SRVROLEMEMBER (‘sysadmin’), 0)

exec sp_executesql N’SELECT name FROM msdb.sys.sysdatabases WHERE name = @dbname’,N’@dbname nvarchar(6)’,@dbname=N’SSISDB’

declare @key_value nvarchar(1024); exec master.dbo.xp_regread ‘HKEY_LOCAL_MACHINE’,’SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\Setup\DTSPath’, N”, @key_value output;select @key_value;

DECLARE @CatalogFileExists bit
if object_id(‘tempdb..#t’) is not null drop table #t
BEGIN
       DECLARE @CatalogFile nvarchar(1024)
       SELECT @CatalogFile = N’C:\Program Files\Microsoft SQL Server\110\DTS\Binn\SSISDBBackup.bak’
       CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
       INSERT #t EXEC xp_fileexist @CatalogFile
       SELECT TOP 1 @CatalogFileExists=file_exists from #t
END
SELECT @CatalogFileExists
— check whether the database is already exist and with correct version
SET NOCOUNT ON

IF DB_ID(‘SSISDB’) IS NOT NULL
    RAISERROR(27135, 16, 1, ‘SSISDB’)

IF SUBSTRING (CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(20)),1,2) <> ’11’
    RAISERROR (27193,16,1, ‘Denali’) WITH NOWAIT

SELECT [value_in_use] FROM sys.configurations WHERE [name] = ‘clr enabled’
DECLARE @path nvarchar(1024) = Convert(nvarchar(1024),ServerProperty(‘MasterFile’)); SELECT @path = SUBSTRING(@path, 1, CHARINDEX(N’master.mdf’, LOWER(@path)) – 1); SELECT @path;
–DECLARE @CatalogFileExists bit
if object_id(‘tempdb..#t’) is not null drop table #t
BEGIN
       –DECLARE @CatalogFile nvarchar(1024)
       SELECT @CatalogFile = N’D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.mdf’
       cREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
       INSERT #t EXEC xp_fileexist @CatalogFile
       SELECT TOP 1 @CatalogFileExists=file_exists from #t
END
SELECT @CatalogFileExists
–DECLARE @CatalogFileExists bit
if object_id(‘tempdb..#t’) is not null drop table #t
BEGIN
       –DECLARE @CatalogFile nvarchar(1024)
       SELECT @CatalogFile = N’D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.ldf’
       CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
       INSERT #t EXEC xp_fileexist @CatalogFile
       SELECT TOP 1 @CatalogFileExists=file_exists from #t
       DROP TABLE #t
END
SELECT @CatalogFileExists
exec sp_executesql N’RESTORE FILELISTONLY FROM DISK = @backupfile’,N’@backupfile nvarchar(67)’,@backupfile=N’C:\Program Files\Microsoft SQL Server\110\DTS\Binn\SSISDBBackup.bak’
exec sp_executesql N’RESTORE DATABASE @databaseName FROM DISK = @backupFile  WITH REPLACE, MOVE @dataName TO @dataFilePath, MOVE @logName TO @logFilePath’,N’@databaseName nvarchar(6),@dataName nvarchar(4),@dataFilePath nvarchar(65),@logName nvarchar(3),@logFilePath nvarchar(65),@backupFile nvarchar(67)’,@databaseName=N’SSISDB’,@dataName=N’data’,@dataFilePath=N’D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.mdf’,@logName=N’log’,@logFilePath=N’D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.ldf’,@backupFile=N’C:\Program Files\Microsoft SQL Server\110\DTS\Binn\SSISDBBackup.bak’
GO
————————————————————————-

USE master; 

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name]=’SSISDB’ AND [is_read_only] = 1)  ALTER DATABASE [SSISDB] SET READ_WRITE WITH ROLLBACK IMMEDIATE

USE [SSISDB];

IF EXISTS (SELECT [name] FROM sys.symmetric_keys WHERE [name] = ‘##MS_DatabaseMasterKey##’)
    DROP MASTER KEY

exec sp_executesql N’USE [SSISDB];DECLARE @pwd nvarchar(4000) = REPLACE(@password, N””””, N””””””);EXEC(”CREATE MASTER KEY ENCRYPTION BY PASSWORD = ””” + @pwd + ””””);’,N’@password nvarchar(10)’,@password=N’Password@1′
GO
——————————————————————————-

USE master

   IF NOT EXISTS(SELECT * FROM sys.asymmetric_keys WHERE name = ‘MS_SQLEnableSystemAssemblyLoadingKey’)
        CREATE ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey FROM EXECUTABLE FILE = ‘C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll’

   IF EXISTS(SELECT [name] FROM sys.server_principals where name = ‘##MS_SQLEnableSystemAssemblyLoadingUser##’)
        DROP LOGIN ##MS_SQLEnableSystemAssemblyLoadingUser##

  CREATE LOGIN ##MS_SQLEnableSystemAssemblyLoadingUser## FROM ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey
    GRANT UNSAFE ASSEMBLY TO ##MS_SQLEnableSystemAssemblyLoadingUser##

  IF EXISTS(SELECT name FROM sys.procedures WHERE name=N’sp_ssis_startup’)
    BEGIN
       EXEC sp_procoption N’sp_ssis_startup’,’startup’,’off’
       DROP PROCEDURE [sp_ssis_startup]
    END
GO

   CREATE PROCEDURE [dbo].[sp_ssis_startup]
    AS
    SET NOCOUNT ON
        /* Currently, the IS Store name is ‘SSISDB’ */
        IF DB_ID(‘SSISDB’) IS NULL
            RETURN
        IF NOT EXISTS(SELECT name FROM [SSISDB].sys.procedures WHERE name=N’startup’)
            RETURN
        /*Invoke the procedure in SSISDB  */
        EXEC [SSISDB].[catalog].[startup]

   IF (0=1)
    BEGIN
        /* Run sp_ssis_startup when Sql Server restarts */
        EXEC sp_procoption N’sp_ssis_startup’,’startup’,’on’
    END
GO
——————————————————————————–

–USE msdb

–IF EXISTS (SELECT name FROM sysjobs WHERE name = N’SSIS Server Maintenance Job’)
–EXEC sp_delete_job
—    @job_name = N’SSIS Server Maintenance Job’ ;

–IF EXISTS(SELECT * FROM sys.server_principals where name = ‘##MS_SSISServerCleanupJobLogin##’)
—    DROP LOGIN ##MS_SSISServerCleanupJobLogin##

–DECLARE @loginPassword nvarchar(256)
–set @loginPassword = ‘Password@1’

–SELECT @loginPassword = REPLACE (CONVERT( nvarchar(256), CRYPT_GEN_RANDOM( 64 )), N””, N”””)
–EXEC (‘CREATE LOGIN ##MS_SSISServerCleanupJobLogin## WITH PASSWORD =”’ +@loginPassword + ”’, CHECK_POLICY = ON’)

 

There, at the end of the script, is the cause of the consternation. So it can run everything successfully except for that account. I commented out those lines of code and reran the script. Success!!! AWESOME-SAUCE! One catalog ready to do some cool stuff. Note that you could also use this if you just want to create the catalog with t-sql. The last step to do is create the job manually to call those procedures and prevent SSISDB from growing too large.

SIDE NOTE: the main password for the Catalog is on line 161. Make sure you change this if you are using this to create your catalog.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’SSIS Server Maintenance Job’,
        @enabled=1,
        @notify_level_eventlog=2,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N’Runs every day. The job removes operation records from the database that are outside the retention window and maintains a maximum number of versions per project.’,
        @category_name=N'[Uncategorized (Local)]’,
        @owner_login_name=N’DOMAIN\USERNAME‘, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [SSIS Server Operation Records Maintenance]    Script Date: 2/27/2015 8:17:49 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’SSIS Server Operation Records Maintenance’,
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=3,
        @retry_interval=3,
        @os_run_priority=0, @subsystem=N’TSQL’,
        @command=N’EXEC [internal].[cleanup_server_retention_window]’,
        @database_name=N’SSISDB’,
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’SSIS Server Max Version Per Project Maintenance’,
        @step_id=2,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=3,
        @retry_interval=3,
        @os_run_priority=0, @subsystem=N’TSQL’,
        @command=N’EXEC [internal].[cleanup_server_project_version]’,
        @database_name=N’SSISDB’,
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’SSISDB Scheduler’,
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20001231,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=120000,
        @schedule_uid=N’82cb4d40-977f-480b-9c59-bf7509e16740′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

And there you have it. Creating the SSIS Catalog via t-sql.

 

~Chris

Advertisements