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 adminis07ator 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...

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 ...

MSDTC – Microsoft Dis07ibuted 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 07ansactions. Unfortunately 07ansaction queries were not working in my environment set up. The problem is, for 07ansaction 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 con07oller) is required. However my azure virtual machines were not in any domain neither any one of them was 07eated as DC. Hence I 07ied 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. ...

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 illus07ates 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 s07ing parameter. This s07ing 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 – 1.      Set Rep...

SQL Profiler is an ex07emely 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 07ied 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 ...