Tuesday, 28 November 2017

Schedule SQL Jobs Using Azure Automation account

With the migration to SQL Azure happening on a fast pace i get lot of queries from my clients on how to schedule a SQL Agent job on a SQL Azure database In this Blog We will see how to schedule a SQL how to schedule a SQL Job using Azure Automation Account.

Environment:-

I am going to use my Test server 'Vinothtestsqlserver' and Test database 'VinothSampleDatabase' for this example.



Login into to Azure Subscription of the SQL Server and create new Automation Account from Azure MarketPlace



Once the Automation Account is created, Scroll through the left tab and select Runbooks under Process Automation,


First Step is to create a Credential for the SQL connection, Similar to Credential in SQL Agent or Data Sources in SSIS,  Under Shared Resources on you Automation Account Page, Select Credentials and Create a new Credential as below.


I have created a Credential Named BlogTestCredential for this example,



Now Create New Run book-This is similar to creating a new SQL agent Job


I have named the Runbook 'VinothTestRunBook' and Will be using Powershell Workflow. SQL Jobs can be created with either Powershell or Powershell Workflow.


Once the Runbook is created Your Script Editor will open, My aim for the SQL Job is to run 'SELECT * FROM sys.tables' from the VinothSampleDatabase every Hour.

I have used the below Powershell Script to achieve the output, The Script take Servername and Database as Parameter, then Uses BlogTestCredential using Get-Credential cmdlet to connect the SQL database. It then uses SQLConnection and SQLCommand Class to run the SQL.

Workflow VinothTestRunBook
{

param
    (
        # Fully-qualified name of the Azure DB server 
        [parameter(Mandatory=$true)] 
        [string] $SqlServerName,
        
        [parameter(Mandatory=$true)] 
        [string] $DataBaseName

        # Credentials for $SqlServerName stored as an Azure Automation credential asset
        # When using in the Azure Automation UI, please enter the name of the credential asset for the "Credential" parameter
        #[parameter(Mandatory=$true)] 
        #[PSCredential] $Credential
    )

     inlinescript
    {
        $Credential = Get-AutomationPSCredential -Name 'BlogTestCredential'
        # Setup credentials   
        $ServerName = $Using:SqlServerName
        $DBName = $using:DataBaseName
        $UserId = $Credential.UserName
        $Password = ($Credential).GetNetworkCredential().Password

        # Create connection to Master DB
        $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
        $MasterDatabaseConnection.ConnectionString = "Server = $ServerName; Database = $DBName; User ID = $UserId; Password = $Password;"
        $MasterDatabaseConnection.Open();

        # Create command to query the current size of active databases in $ServerName
        $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
        $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
        $MasterDatabaseCommand.CommandText = 
        "SELECT * from sys.tables"
                
        # Execute reader and return tuples of results 
        #$MasterDbResult = $MasterDatabaseCommand.ExecuteReader()
        #if ($MasterDbResult.HasRows)
        #{
              #$MasterDbResult.Read()|SELECT *
        #}
        # # Execute the query
        #$DatabaseCommand.ExecuteNonQuery() #ToExec SP
        $Ds=New-Object system.Data.DataSet
        $Da=New-Object system.Data.SqlClient.SqlDataAdapter($MasterDatabaseCommand)
        [void]$Da.fill($Ds)

        # Output the count
        #$Ds.Tables.Column1
        $Ds.Tables[0]|ft -a

        $MasterDatabaseConnection.Close
    }

}

In the Above Example I have used SQL Adapter to get the output and display, But in case you want to run a Store Proc or run Updates or Deletes use the Appropriate method(eg. ExecuteReader(), ExecuteNonQuery(), ExecuteScalar() etc.. More details on SQLCommand class and method in the below MSDN Link.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand%28v=vs.110%29.aspx


Once you Finish Scripting Test the Script by Clicking the Test Pane Button, where you can test the script by specifying the parameters(Server name and DB Name in this case) as below.


Once your testing Successful, Publish your run book by clicking the publish button this is same as creating a SQL Agent Job without a Schedule. 


Now once your Runbook is publish we can create a Schedule, Go back to Runbook Page on the Portal and select Schedules and create a new schedule as below I have created similar to SQL Agent job to run this script every hour,



Once you Create the Schedule Specify the Parameters for the Run book Schedule as below,


That's about it your job is now ready to run, To check the Job history Go to Workbook page in the Portal and click Jobs


Now you can see summary of a completed job, for detailed information click on the summary


And You can see the Jod Run's detailed info, To see the output click the output pane and you can see the job output.



There you go now you have perfectly working SQL Agent Environment in Azure. Powershell Workflow Allows you you run Steps in Parallel and in sequence we will see in detail on future blogs.





Monday, 11 September 2017

Replication - Compatibility issue

I was working on a POC concept for my client to test some replication functionality and got the below error,



"he selected subscriber does not satisfy the minimum version compatibility level"

I was using SQL Server 2008 as a distributor and Publisher and SQL Server 2012 as subscriber to mimic my client environment and i was using Developer edition to carry out my test.  I very well know the version and edition i was using supports replication. Finally after some research i found i was using SQL 2008R2 SSMS to set up my subscriber with was the cause of the mismatch, once i switched to SQL2012 SSMS everything was running fine. After this i did a testing with SQL 2012,SQL 2014 and SQL2016 similarly using publisher a one level below the subscriber and all the version failed with the same error when i use the lower version ssms. Hope the information helps.

Tuesday, 27 June 2017

Run SSMS As User in Different Domain

Every come across the trouble of having to login to a jump server in just to access SSMS since the server is in different domain. There is a way to by pass it and access SSMS using your second domain login from your desktop by using Runas /NetOnly option irrespective of domain trust.

Use the below command in the Command Prompt:-

runas /netonly /user:<domain\username> "<SSMSbasepath>\ssms.exe"

SSMS base path is the folder where your SSMS.exe is located.

More Info https://technet.microsoft.com/en-gb/library/bb490994.aspx




Tuesday, 30 May 2017

Find SSAS instance Port Number

When you have more than one instance of SSAS installed in your Server you might end up in a situation to find the port numbers for each instance to set the firewall settings on the server. As you might all know the default Port for SSAS is TCP 2383, 

To find the port number of the other instances search for the msmdredir.ini file on your ProgramFiles(X86)->Microsoft SQL Server folder, most probably the file resides in the (%ProgramFiles(X86)%\Microsoft SQL Server\90\Shared\ASConfig\msmdredir.ini)location, Look in both 64 bit and 32 bit Program Files folder but more likely to be in 32 bit folder even for 64 bit installations.

If you have more than 1 Instance you will have entry in the ini file like below, If you change the port number to 0 it would take dynamic ports else you can assign a static port to the instance.

<Instances>
<Instance>
<Name>InstanceName</Name>
<Port>64931</Port>
<PortIPv6>64931</PortIPv6>
</Instance>

</Instances>

The Above exercise will hold good only for Non-Clustered SSAS instance, Clustered SSAS instance always uses TCP Port 2383 with the corresponding SSAS Cluster Resouce IP.

Thursday, 23 March 2017

SQL Azure database - dbmanager and loginmanager role

Recently I was working for a client to install a new application with a backend on SQL Azure (Paas), The application was an inbuilt application which creates a database during application and does not have an option of using existing database, They did not want to use the Server Admin Login to accomplish this task. Unlike traditional SQL Server you can configure only one server Admin in the Azure Portal for SQL Azure Database as below.



And Since we do not have a server level entity in Azure we do not have any server level roles to provide a user with dbcreator. There alternate way to accomplish this according to MSDN( https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins ) there are two 2 new roles in the master database for SQL Server (dbmanager and loginmanager)


'dbmanager' allows users to create databases in Azure environment and loginmanager as the name suggests allows you to create new login without being a serveradmin. All you need to do is to create a login and corresponding user in master and grant it dbmanager role for the user to get dbcreator rights on SQL Azure, once we did this we were able to install the application without any issues and the new DB was created successfully.

Tuesday, 21 February 2017

Quick Tips:- Add SQL User/Rolemember to all Databases in SQL Server

Most of times when you have dedicated SQL Server for an Application clients come up with a request to add a user to all databases in the SQL Server with certain permission, while its east to do it via SSMS for if the system has only couple of databases but imagine your SQL instance have hundreds of databases then scripting is the way to go, I have assumed the login name is ‘Test’ client has request db_datareader and db_datawriter permission for our example blog,

There are 2 ways I am going to accomplish this one using TSQL and another using Powershell.

TSQL:-
--Add User
EXEC sp_MSforeachdb 'USE ?;CREATE USER Test FOR LOGIN Test'
GO
--Add Role member
EXEC sp_MSforeachdb 'USE ?;ALTER ROLE db_datareader ADD MEMBER Test'
GO
--Add Role member
EXEC sp_MSforeachdb 'USE ?;ALTER ROLE db_datawriter ADD MEMBER Test'

Powershell:-

Clear-Host

Import-Module SQLPS -DisableNameChecking -ErrorAction SilentlyContinue
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "1MKBG12"
$dbs = $srv.databases


$loginname = 'Test' ##########Provide Login name to check#############


$chkloginflag = $null
$chkloginflag = $srv.Logins|where {$_.name -eq 'Test3'}
if($chkloginflag -eq $null -or $chkloginflag -eq '')
{
      $login = New-Object ('Microsoft.SqlServer.Management.Smo.Login') ($srv, $loginname)
      $login.LoginType = 'SqlLogin' #https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.logintype.aspx
      $login.PasswordExpirationEnabled = $false
      $Login.PasswordPolicyEnforced = $false
      $login.Create("test")

      forEach($db in $dbs)
      {
            $usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $loginname)
            $usr.create()
            $usr.AddToRole("db_datareader")
            $usr.AddToRole("db_datawriter")
      }
}


The above PowerShell script also creates a Login if it does not exist then creates the SQL user in all databases with reader and writer roles. I have written a simple script assuming the Login to be SQL Authentication. You can tweak the $Login object to create Windows User/Group Login.







Tuesday, 18 October 2016

There is no remote user ‘xxxxx’ mapped to local user ‘(null)’ from the remote server ‘xxxxx’

My Client recently complained that his linked server was failing to connect with the following error,

“An error Occurred during Service Key decryption
There is no remote user ‘xxxxx’ mapped to local user ‘(null)’ from the remote server ‘xxxxx’.
(Microsoft SQL Server, Error : 33094)”

On checking the user permissions were right and the remote login in which the Log server was configured had right permissions to access to the Linked server.

When I did some googling I stumbled across, https://technet.microsoft.com/en-us/library/ms187788.aspx

To change the SQL Server service account, use SQL Server Configuration Manager. To manage a change of the service account, SQL Server stores a redundant copy of the service master key protected by the machine account that has the necessary permissions granted to the SQL Server service group. If the computer is rebuilt, the same domain user that was previously used by the service account can recover the service master key. This does not work with local accounts or the Local System, Local Service, or Network Service accounts. When you are moving SQL Server to another computer, migrate the service master key by using backup and restore.

The REGENERATE phrase regenerates the service master key. When the service master key is regenerated, SQL Server decrypts all the keys that have been encrypted with it, and then encrypts them with the new service master key. This is a resource-intensive operation. You should schedule this operation during a period of low demand, unless the key has been compromised. If any one of the decryptions fail, the whole statement fails.

The FORCE option causes the key regeneration process to continue even if the process cannot retrieve the current master key, or cannot decrypt all the private keys that are encrypted with it. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement.”

This is exactly what happened with my client, the SQL was running under local account and the server was migrated.

Once I ran ALTER SERVICE MASTER KEY FORCE REGENERATE the linked servers started working gain.