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.

AlwaysOn Monitoring

Secondary Replica Health

0-NOT SYNCHRONIZING,1-partially healthy,2-healthy

select  synchronization_health secondaryreplicahealth
from  sys.dm_hadr_availability_replica_states
where  role_desc = 'secondary'

Primary Replica Health

--preferred secondary backup

declare @DBName as varchar(150)
declare @preferredReplica as int
SET  @DBName = (select top 1 name from sys.databases where database_id > 4)
SET  @PreferredReplica =(Select master.sys.fn_hadr_backup_is_preferred_replica(@DBName))

 --print @preferredReplica

IF
(@preferredReplica = 0 )
BEGIN
select
synchronization_health Primaryreplicahealth from sys.dm_hadr_availability_replica_states
where role_desc = 'primary'
END

 IF (@preferredReplica = 1 )
BEGIN
select
 99
END

Replica Custernode status
0 = Not joined
1 = Joined, standalone instance
2 = Joined, failover cluster instance

select
distinct  min(join_State) from
sys.dm_hadr_availability_replica_cluster_states
where  join_state_desc ='JOINED_STANDALONE'

--Listener Status

--IP resource ONLINE/OFFLINE state from the WSFC cluster, one of:

-- 0= Offline. IP resource is offline.

-- 1 = Failed. IP resource was being brought online but failed.

-- 2 = Online Pending. IP resource is offline but is being brought online.

-- 3 = Online. IP resource is online.

　

select  'state' = case 
when  state_desc = 'OFFLINE' then 0
when  state_desc = 'FAILED' then 1
when  state_desc = 'ONLINE PENDING' then 2
when  state_desc = 'ONLINE' then 3
end  from
sys.availability_group_listener_ip_addresses

 

Data Loss Time (RPO)

 

AlwaysOn CheckList

Windows Server 2012 and SQL Server 2012 AlwaysOn availability group

SQL Server 2012 AlwaysOn availability group is HOT!!!!
-Use Windows Server 2012
-DO NOT Use Windows Server 2008 R2 ( BAD BAD BAD Idea!)

 Before we start on availability groups, we need to prepare for the following checklist.

Ø  Windows Server Operating system version & edition

Ø  Processor

Ø  Memory

Ø  Data/Log/Temp/Backup drives Configuration for SQL Server

Ø  Central backup share for both primary and secondary replicas

Ø  Windows recommended hot fixes  For Windows 2012: http://support.microsoft.com/kb/2784261

Ø  Public static ip addresses for private network in primary and secondary replica

Ø  Private ip address for public network in primary and secondary replica

Ø  Virtual cluster name and virtual cluster ip address

Ø  Number of nodes on a cluster

Ø  Quorum model and CIF share configuration

Ø  SQL server Enterprise Edition

Ø  SQL Server Cu updates

Ø  Availability groups Names

Ø  Reserved availability group Listener names and listener static IP Addresses

Ø  Number of availability groups on primary replica

Ø  Number of databases on each availability group

Please read the following links before we start AlwaysOn.
Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQLServer)
http://msdn.microsoft.com/en-us/library/ff878487.aspx#RestrictionsAG

Note : AlwaysOn availability groups do not work with distributed transactions. (MSDTC)

Inside AlwaysOn

Monitor Performance for AlwaysOn Availability Groups

I am referring to http://technet.microsoft.com/en-us/library/dn135338.aspx
Availability groups in SQL server 2012 is a hot topic. Recently, I have set up and configured availability groups at my work. I have SQL server 2012 enterprise version on Windows 2012 Data Center Edition.

We will need to know how primary and secondary servers are communicating to each other in order to monitor performance of availability groups.


 Data Synchronization Process







1 . Log Flush

Log data is flushed to disk. This log must be replicated to the secondary replicas. The log records enter the send queue.


select * from sys.dm_os_performance_counters
where counter_name like '%log bytes Flushed/sec%' 

2. Log Capture

Logs for each database is captured and sent to the corresponding partner queue (one per database-replica pair). This capture process runs continuously as long as the availability replica is connected and data movement is not suspended for any reason, and the database-replica pair is shown to be either Synchronizing or Synchronized. If the capture process is not able to scan and enqueue the messages fast enough, the log send queue builds up.

select * from sys.dm_os_performance_counters where counter_name like '%bytes sent to Replica/sec%' 

3. Send

The messages in each database-replica queue is dequeued and sent across the wire to the respective secondary replica.

select  * from sys.dm_os_performance_counters where
counter_name like '%bytes sent to transport/sec%'


4. Received and Cached

Each secondary replica receives and caches the message.
select  * from sys.dm_os_performance_counters where
counter_name like '%log bytes received/sec%' 

5. Harden
Log is flushed on the secondary replica for hardening. After the log flush, an acknowledgement is sent back to the primary replica. Once the log is hardened, data loss is avoided. 


select  *  from sys.dm_os_wait_stats
where  wait_type = 'HADR_LOGCAPTURE_SYNC'
select  * from sys.dm_os_performance_counters
where  counter_name like '%log bytes Flushed/sec%'


6. Redo

Redo the flushed pages on the secondary replica. Pages are kept in the redo queue as they wait to be redone.


select  * from sys.dm_os_performance_counters
where  counter_name like '%redone bytes/sec%'

select  * from sys.dm_os_wait_stats  where  wait_type = 'REDO_THREAD_SYNC'


Flow Control Gates

AlwaysOn Availability Groups is designed with flow control gates on the primary replica to avoid excessive resource consumption, such as network and memory resources, on all availability replicas. These flow control gates do not affect the synchronization health state of the availability replicas, but they can affect the overall performance of your availability databases, including RPO.

how many times flow control was activated and how much time was spent waiting on flow control. Higher wait time on the flow control translate to higher RPO.


select  * from sys.dm_os_performance_counters  where  counter_name like '%Flow control%'






sp_MSforeachtable

Today, Grant Fritchey tweeted about sp_MSforeachtable . What will we see if we run the following statement on Azure database?

EXEC sp_msforeachtable 'select ''?'', count(*) from ?'

It retruns
Could not find stored procedure sp_msforeachtable.

It is deprecated on Azure. Then, I tried to run on local development database. It returns total record count in each table in database. It will be useful for a DBA in future. Next step, I looked up sp_Msforeachtable in Google. I found two more useful scripts.

Checking integrity of tables in database

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])';

Checking space used of each table in database

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';


Checking space used of each database

declare @cmd varchar(500)
set @cmd='use [?];exec sp_spaceused '

exec sp_MSforeachdb @cmd