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.