Assign user to single database in Azure SQL Database

Security is vast topic in case of SQL Azure. In this post I will explain simple steps required to create user, assign db owner role to that user specific to single Azure SQL DB.

The advantage is, you don’t have to use admin credentials of Azure SQL DB server. Also the new use will not have any access to master database and hence accidental changes to other database are avoided.  

First login to Azure SQL management portal. Login to SQL Azure management portal using credentials of user which has access to master database. Such user can be server administrator for Azure SQL.

Then create a sample database. I will name it as “MyDB”. Now I need to create a user in such a way that it will have permission to perform any operation on MyDB only. So same user credentials can be used in applications connecting to SQL Azure.

Steps are as follows –

Create Login in master database query window.

Create Login kunal WITH PASSWORD = ‘yourPassword’ 

Create user in query window of MyDB database from above created login.

Create User kunalasuser FROM LOGIN kunal 

Connecting to SQL Server Virtual Machine on windows Azure from another windows Azure virtual Machine or on-premise computer – Using simple Powershell Script

With the new offering of IaaS on Azure, you can provision SQL server on windows Azure virtual

machine. Once SQL Server virtual machine provisioned you need to complete the configuration steps mentioned here - http://www.windowsazure.com/en-us/documentation/articles/virtual-machines-provision-sql-server/#SSMS 

Now same link suggests using SSMS (SQL Server Management Studio) if you wish to connect from another computer to SQL Server Azure virtual machine. What if you don’t have SQL Server management studio installed? What if the computer you wish to connect is Azure Virtual Machine and you don’t wish to install management studio on it? In such scenario the best option is to use powershell script to check the connectivity to SQL Server Azure Virtual Machine. 

Below I have written a simple powershell script which you can run from any machine to check the connectivity with Azure Virtual Machine SQL Server.

First open the powershell from programs menu as shown below –

Then run following script to set execution policy. This will ask you Y or N. Select Y to proceed. 

#set execution policy

set-executionpolicy remotesigned 

After this copy and paste the following script in powershell window. Make sure that you replace the Server name below yellow marked and port number, user name and password. 

function IsSQLDBAvailable([string] $SQLServer)

{

            try

            {

                        $Connection = New-Object System.Data.SQLClient.SQLConnection

                        $Connection.ConnectionString = "server=mycloudservice.cloudapp.net,57500;Database=Master;User Id=myuser;Password=mypassword;"

                        $Connection.Open()

                        return $true;

            }

            catch [System.Exception]

            {

                        return $false;

            }

}

$SSODB = $False

while ($SSODB -eq $False)

{

$SSODB = IsSQLDBAvailable ("mycloudservice.cloudapp.net,57500")

            start-sleep -s 2

            write-host "Waiting for SQL DB ..."

}

write-host " Connected to SQL server - $SSODB `r`n"

 

If the connectivity to SQL server is successful then you will receive message as  -

Connected to SQL Server – True.

If the machine is not able to connect to SQL Server Azure Virtual  machine then you will keep receiving message as –Waiting for SQL DB….”

This is the simplest way by which you can check the connectivity to SQL Server on Azure Virtual Machine. And best part is you don’t have to install SSMS.

Note - If you are trying to connect to SQL Azure Virtual Machine from the computer which is part of your company’s or corporate network then for security purpose the non standard ports are blocked (like 57500 mentioned in the above configuration link.). In that case you may not be able to connect to SQL Server Virtual machine using above powershell or SSMS. Best way is configure the TCP endpoint of SQL Server Azure Virtual Machine on public port as 80 and private port as 1433. Usually port 80 is not blocked in any organization. Hence you will be able to test the connectivity of Azure Virtual Machine SQL Server on 80 port. 

Hope this helps.

Cheers…

Happy Connecting!!!

Definitive steps to configure MSDTC on Azure virtual machines – without need of domain controller

MSDTC – Microsoft Distributed Transaction.

I had requirement to configure Azure Virtual machine with SQL Server 2008 R2 and One Azure Virtual machine as an application server having few sites hosted on it. Azure virtual machine app server hosted application should be able to connect to SQL server and run SQL queries with transactions. Unfortunately transaction queries were not working in my environment set up.

The problem is, for transaction to work between SQL server and application server you need to have MSDTC to be configured on both machines. Everywhere I read through many posts that for configuring MSDTC, settings related to AD DC (domain controller) is required. However my azure virtual machines were not in any domain neither any one of them was treated as DC.

Hence I tried to configure MSDTC on azure virtual machines without domain configuration and it was successful.

Following steps will guide through the steps required to enable MSDTC on Azure virtual machines. Please note that the azure virtual machines do not have any domain configured on them neither they are not part of any domain.

I have 2 azure virtual machines with following configuration –

1.     SQL Server 2008 R2

2.     Windows Server 2008 R2 – call it as app server

Now I need to configure MSDTC on SQL server and app server.

At first you need to configure Azure virtual network as per the guidelines mentioned at this post.

http://www.windowsazure.com/en-us/documentation/articles/create-virtual-network/ 

Then provision Azure virtual machines with above configuration using the steps mentioned at this post in the virtual network you created above.

http://www.windowsazure.com/en-us/documentation/articles/virtual-networks-add-virtual-machine/ 

Now RDP to both Azure Virtual machines from the steps mentioned in this post.

http://www.windowsazure.com/en-us/documentation/articles/virtual-machines-windows-tutorial/#logon 

Then on SQL server make configuration settings mentioned on this post - http://www.windowsazure.com/en-us/documentation/articles/virtual-machines-provision-sql-server/#SSMS

Then log in to App server and follow below steps.

***MSDTC configuration***

Select Open Control Panel -> System and Security -> Allow a program through windows firewall option as shown below.

Automated deployment of .rdl files to SQL Server 2012 Reporting Services using c# and Reporting web Service of SSRS 2012

I was searching for automated deployment of .rdl [report definition language] files on SSRS reporting server 2012. Everywhere I found the reporting service example which was prior to 2012. Following code illustrates how we can deploy .rdl files to SSRS 2012. 

SSRS 2012 report service has URL same as 2010. Therefore the report service URL will be as follows –

http://ServerNameHavingSQL2012:PortNumber/reportserver/reportservice2010.asmx

If you have hosted reporting service on default port 80 then you don’t need to provide PortNumber in above URL. I have written a class to make automated deployment of .rdl files. Let’s call it as RDLDeployer. I have added a public method which takes string parameter. This string parameter can be the path of .rdl file to be deployed on SSRS 2012.

First add reference of SSRS web service in your application. Then use following code to deploy the .rdl file on report server 2012.

Steps are as follows –

How to open SQL profiler in SQL Express LocalDB

SQL Profiler is an extremely wonderful tool to debug the SQL server problems. I personally love this tool very much. Recently while working with one of the project I had to deal with SQL Express LocalDB instance. According to my coding style I was very much sure that, I will need to debug at SQL level using SQL profiler to fix the issues.

As expected one of my stored procedure in SQL Express LocalDB was failing and I tried to debug it using SQL Profiler. You can open the SQL profiler in SQL Server Management Studio from “Tools” menu.

The login window appears where in you will need to put the SQL Express LocalDB server instance name. As a usual practice you may put the server name like for example, (localDB)\MyDB with connection option as Windows Authentication or with credentials for SQL Server authentication. However with this approach the SQL profiler may not open.

To overcome this problem we need to use SQLLocalDB utility. SQLLocalDB utility is command line tool which helps user to manage and perform administrative operations on SQL Express LocalDB instance. Here to connect SQL profiler to LocalDB instance we need to retrieve the correct instance name using SQLLocalDB utility. Open the command prompt and type following line in command line window.

Sqllocaldb info “InstanceName”

InstanceName in above command will be replaced by the instance name of your LocalDB server instance. The “info” command argument return you name, version, and state, last start time and most important “Local Pipe Name” of the specified LocalDB instance. The information is as shown below –

 

The local pipe name selected and reported in above command output need to be used for making the connection in SQL Server Profiler for SQL Express LocalDB as shown below –