Granting Windows AD group to access to Integration Services Catalog in SQL Server 2012

Issues

Though we give access to AD group in SSIS Package folder directly, the AD group members are not able to see SSIS packages under Integration Service Catalogs.  I did a little bit of research and found that Microsoft has a bug in Integration Service Catalogs security. The issues will exist if you do not upgrade SQL server 2012 to SP1. Since we are not upgrading SQL server to SP1, we follow workaround steps from Microsoft.

http://support.microsoft.com/kb/2890052

First, add AD Group group login in Security on Instance level

USE master
GO

CREATE LOGIN domain\username FROM WINDOWS WITH DEFAULT_DATABASE=master
GO

USE SSISDB
GO

CREATE USER domain\username FOR LOGIN domain\username
GO

Create SSIS_users role in SSISDB

use SSISDB

Go

create role ssis_users

go

Add Domain login to ssis_users

USE SSISDB

GO

ALTER ROLE ssis_users ADD MEMBER domain\username

After you run all the scripts above,

• Right click on your SSIS package folder under Integration Services Catalogs

• Click on Permissions and browse and check ssis_users role. It will be added under logins or roles tab

• Then, give appropriate access to ssis_users under Explicit tab. That's it.  Now, members of AD groups can view SSIS packages.

Connecting to a database using Domain account via SMS

I have my windows login given by my company. I use my windows authentication to login to my computer and then connect to any databases via SQL Server Management Studio(SMS) . 

As a DBA, I will need to grant a proper access a domain account (service accounts for some applications) to databases. Sometimes, they have issues connecting to databases though I give them proper access. So, I make sure that the domain account which is given access to my database is able to connect to the database.

In order to connect to database using a domain account,

First, I make a shortcut of SQL Server Management Studio on my desktop.

Second,  update the Target command with the following command. The path "c:\program files..." might be different because it depends on the location of your SQL server management studio is installed 

C:\Windows\System32\runas.exe /netonly /user:DOMAIN\ACCOUNTNAME "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Then, double click on the shortcut and enter domain account password. 

SQL Server Management Studio will open and you will see your windows login(domain\yourlogin) but you are connected to a database as the domain account. Type sp_who2 in on the server and you can see your login as a domain account.

SQL Server 2012 AlwaysOn Testing

Preparation

Use SQL server management studio (SMS), create a test database and add the database to availability group database.

create database test

create table test

(testno int)

Data Load script

I will use the following simple data load script to test data synchronization and connection.

declare @i as integer

declare @id as integer

set @i = 200000

set @id = 1

insert into test(testno)

select @id

while ((select max(testno) from test) < @i )

begin

insert into test(testno)

select @id

set @id = @id +1

print @id

end

Test Case

Test 1: Ensure synchronization works normal

• Connect to Availability group Listener and run data load script
• Check the secondary replica to see if the log records made it across

Expected Result :Logs are written to Secondary replica. Row counts are the same.

Test 2: Primary replica with Manual failover

• Start manual failover from AlwaysOn Dashboard
• Confirm Primary is successfully failover to Secondary replica
• Reconnect to Availability group Listener

Expected Error : In SMS,Remote harden of transaction 'INSERT' (ID 0x0000000007016159 0000:000199ce) started at Jan 17 2014  2:04PM in database 'test' at LSN (211:148:3) failed.

Expected Result: Failover successfully. During the failover, client connection is disconnected to Availability group listener. Reconnect to Listener is successful and data load continues. Connection lost during failover is expected by design. It is between 3 sec to 15 sec.

Test 3: Primary replica with Manual failover (cont.): missing rows to determine the data loss

• Start manual failover from AlwaysOn Dashboard while data load script is running
•  Determine any missing rows are not submitted to Primary
•  Determine any missing rows are not replicated to Secondary

Expected Result: No missing rows in primary and secondary replicas

Test 4: Primary replica with Automatic (forced) failover

4.1    Stopping SQL Server service

• Stopping SQL Server service
• Confirm secondary replica becomes primary replica
•  At this point, primary replica run exposed and RPO of 0 data loss is not met because there is no secondary replica
• Reconnect to listener and run data load script

Expected Result: In Failover Cluster Manager, Secondary replica becomes Primary. During timeout, client connection is disconnected to Availability group listener. Then, reconnect to Listener is successful and data load continues.

4.2    Power down Primary replica

• Power down Primary replica from VSphere
• Confirm secondary replica becomes primary replica
•  Reconnect to listener and run data load script

Expected Result: In Failover Cluster Manager, Secondary replica becomes Primary. During timeout, client connection is disconnected to Availability group listener. Then, reconnect to Listener is successful and data load continues

4.3    Stop Current owner Cluster service

• Stop a current owner cluster service from failover cluster manager
• Confirm secondary replica becomes primary replica
• Reconnect to listener and run data load script

Expected Result: In Failover Cluster Manager, Secondary replica becomes Primary. During timeout, client connection is disconnected to Availability group listener. Then, reconnect to Listener is successful and data load continues.

4.4    Disable Primary server network adapter

• Open Network Connection on Primary Replica
• Click on both device and disable
• Confirm secondary replica becomes primary replica
• Reconnect to listener and run data load script
• Open VShpere and open console of earlier Primary replica and enable network adapers

Expected Result: In Failover Cluster Manager, Secondary replica becomes Primary. During timeout, client connection is disconnected to Availability group listener. Then, reconnect to Listener is successful and data load continues.

Test 5: Primary replica with Automatic failover (cont.): missing rows to determine the data loss

• During automatic forced failover cases (4.1 – 4.4)
• Determine any missing rows are not submitted to Primary
• Determine any missing rows are not replicated to Secondary

Expected Result : No missing rows

Test 6: Secondary replica down

• Connect to availability group listener and run data load script
• Power off Secondary replica
• Confirm data load script is not interrupted

 Expected Result: Data load is NOT interrupted.

Test 7:  Primary replica also fails and Secondary comes up later

Imagine due to power issues, Primary and Secondary replicas are down.

• In Test 6, secondary replica is down
• Now Power off Primary
• Both nodes are offline and Cluster is down
• After 1 minute, power on earlier Secondary
• Confirm earlier Secondary comes up as a primary replica
• Confirm Cluster service comes up
• Reconnect to Listener and run data load

Expected Result : Earlier Secondary replica is online but it doesn’t change the role to Primary. Databases are Recovery pending state in Secondary. Unable to connect to Listener. The failover cluster manager is unable to connect to our WSFC.

Test 8:  Bring earlier Primary replica back online

• Power on earlier primary replica
• Confirm Cluster service is up
• Reconnect to Listener and run data load

Expected Result : Earlier Primary replica doesn’t come up as a secondary. It role is still Primary. Data load started and database is synchronized.

Test 9:  Quorum is down

• Take quorum offline. The WSFC cluster service offline
• Determine how application behaves in this situation
• After 1 min, Bring quorum online
• Confirm cluster service online
• Confirm all nodes are online
• Confirm database are synchronizing
• Confirm SQL Management Studio is able to connect to listener

E    Expected result : The cluster service stays online even if the quorum is taken offline. The quorum configuration is Node and File Share Majority. We had three nodes before the quorum is taken offline.  After the quorum is taken offline, the total node is 2 and we have two votes. We need more than half of the votes to running the cluster service. This means we can only sustain a single node failure.

     Test 10: Primary replica is taken offline while the quorum is 

•   Stop SQL service on Primary Replica
•  Verify a cluster service is still online

      Expected Result : The cluster service is offline. We need at least 2 out of 3 nodes to be online in order to the cluster service to stay online

Test 11:  VMotion

• Test VMotion on both replicas with System Adminstrator team
• Confirm no replica role changes
• Confirm no changes in cluster node role

Expected Result : VMotion is performed with no impact to running workloads or connected users and replica role.

Test 12:  Load testing/ performance test

• Application will log millions of rows
• Take a performance base line of the server while the loading takes place

Ø  IO

Ø  CPU

Ø  Disk

Ø  Network

Ø  SQL Server : Availability group replica

Ø  SQL Server: Availability group Database

Powershell - Copy .bak files

Copy .bak files from Local drive in SQL server to Central share

If you like command line scripting, you will like powershell. We have .bak files located on a backup drive on SQL server. They need to be copied to central share location for disaster recovery. I created the following powershell code. It is scheduled in SQL Agent. One tricky part is if  SQL job agent does not throw errors due to the central share does not exist or files do not not exit. So, we need to add error handling to let us know if the job fails.

cd c:
$originalpath = "S:\SQL\backup\"
$Destinationpath = "\\backups\bak\"
$filespath = "$Destinationpath\backup"
if (!(test-path $filespath))
{
    
    Write-Error "File Path error" -ea Stop
}

if(test-path $filespath)
{
    Remove-item $filespath -recurse -ea STOP
    Try
    {
        Copy-Item -Path $originalpath -filter *.bak -Destination $Destinationpath -force -Recurse -ea STOP -Errorvariable myError
    }
    Catch
    {
        Write-Error "Job Failure with $myError" -ea Stop
    }
   
}

Logins / Credentials / Proxies

Logins/Credentials/Proxies enforce the principle of least privilege.

Recently, we have built three production servers (SQL Server 2012) for Datawarehouse Team.

• SQL Server 2012 Enterprise Edition
• CU3
• SSIS Catalog is installed and configured

Datawarehouse Team built SSIS packages and imported to SSIS Catalog. SSIS packages which are imported in SSIS Catalog are scheduled in SQL Agent. They are run as a windows Integrated Security. Note :A service account running SQL Server service and SQL Agent has sys admin right.

We, DBAs need to change the SSIS job. We will need to use a proxy account which is mapped to a credentital.

I received my information from Jason Strate's web site.

http://www.jasonstrate.com/2013/07/security-questions-logins-credentials-and-proxies/

The next question in the list is:

What is the relationship between logins, credentials, and proxies? And why they were introduced?

Before we get too deep, though, lets defined each of these objects. They are:

• Login: A login is any principal that is granted access to a SQL Server instance. The access can be granted to domain users, domain group, or SQL Server authenticated accounts.
• Credential: A credential provides a mechanism to store login information for a domain account within SQL Server. The credential can then be used to pass that authentication information from into another login or a proxy to grant it permissions to resources external to SQL Server.
• Proxy: A proxy is used by SQL Server Agent to map credentials to SQL Server Agent subsystems (i.e. PowerShell or SSIS).

Ok. In our case,

• Create a login which is a domain account and has a public role to server. It will have db_reader, db_writer, db_ddl rights to database level. (It depends on Data FLow)
• Create a credential which maps to the domain login
• Add the credential to the domain account in login
• Create a proxy which is mapped to the credential in SSIS Package Execution under SQL Agent

SQL Server Management Studio

 Create a Login

First, a domain account login needs to be created. Second, a credential is created using the domain login. Last, add the credential to the login.

 Give public role to the domain account login to SSISDB

Create a Credential

Create a Proxy

 Create Principals

 Msdb role principals are added to the proxy account.  

1. SQLAgentReaderRole
2. SQLAgentUserRole
3. SQLAgentOperatorRole

 

A new proxy account is created

SSIS Catalog Configuration

We gave a public role to the domain account login to connect msdb and SSISDB databases. In order to give access to the proxy account from SQL Agent to SSIS packages stored in SSIS Catalog, we will need to add permission on SSIS packages in SSIS Catalog. In order to do this, Click on a project folder in SSIS Catalog.

Give the folowing permission to the domain account.

• Read, Read Object, Execute Object

Creating a job and run a job as a proxy account

• Create a job in SQL Agent
• In steps, create a step and choose run as a proxy account.

Let the job ran, open a new query window and execute the following.

usessisdb

select  * from catalog.executions

In the query result, look at a column " executed_as_name" which shows the domain account used in credential. So we can confirm that the job is executed by a proxy account which is mapped to the domain account.

SSIS Catalog Reports Permission

SSIS Catalog reports permission

SSIS developers who do not have ssis_admin access are given db_datareader to SSISDB and public role to server. Still they are not able to view ssis execution reports in SSIS Catalog because ssis_Admin access is required to view them. The access right is limited in Catalog views by Microsoft.

 In order to fix the issues, I will need to alter the following views in SSIS catalog.

·         Catalog.event_messages

·         Catalog.executions

To alter catalog.event_messages and catalog.executions,  click on script view as > alter to > new query editor window.

Then , comment  out the following part of the script.

--comment it to give access to reports

--WHERE      opmsg.[operation_id] in (SELECT [id] FROM [internal].[current_user_readable_operations])

--           OR (IS_MEMBER('ssis_admin') = 1)

--           OR (IS_SRVROLEMEMBER('sysadmin') = 1)

After updating views, developer can view executions and drill down messages. Connect to server and you can be able to view reports.

AlwaysOn- PreferredBackup

Which one is a preferred backup replica?

If sys.fn_hadr_backup_is_preferred_replica( @dbname ) <> 1
BEGIN
-- If this is not the preferred replica, exit (probably without error).
END
-- If this is the preferred replica, continue to do the backup.