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.



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.

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



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

      forEach($db in $dbs)
            $usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $loginname)

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.

Thursday, 25 August 2016

SQL Server 2016: SQLServer Powershell Module and Get-SQLErrorlog CMDLET

With the latest update to SQL Server 2016 SSMS there are some new interesting features added to the SQL Server Powershell making managing SQL Server through Powershell more easier and efficient.

The Detailed information on the addition can be found in the below link,


In this blog we are going to see how we can add this new module to our existing software and we are going to look at one interesting CMDLET "Get-SQLErrorlog"

Lets get started with updating the SSMS, Download the latest SSMS from the below link irrespective of if you want to install a new instance of SSMS or upgrade the existing instance

The Install or upgrade should be pretty straight forward, once the installation is complete verify your SSMS Version 13.0.15700.xx and above will have the new SQL Server module.

As mentioned in the Above SQL Server Team Blog (https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/)

"The SQL PowerShell module that ships with SSMS has changed from SQLPS to SqlServer (there is no change to the module used by SQL Agent). This means that if you have a PowerShell script doing Import-Module SQLPS, it will need to be changed to be Import-Module SqlServer in order to take advantage of the new provider functionality and new CMDLETs. The new module will be installed to “%Program Files\WindowsPowerShell\Modules\SqlServer” and hence no update to $env:PSModulePath is required"

So Now lets go and check if the SSMS update has installed the new module

There you go, Now we have the new SQLServer Module installed, Let check it out as i mentioned before I am going use Get-SQLErrorlog CMDLET in the SQLServer Module for the testing

More about Get-SQLErrorlog @ https://technet.microsoft.com/en-us/library/mt759806.aspx

There you Go...We can get the errorlog as simple as that!

Now Let see how we can play with it on Powershell Console, I use PowerGUI as my powershell editor but you can use Windows native editor or any other 3rd party editors for Powershell,

As a First step I try to Import the new module SQLServer but it throws an error as shown in the below image, this is because most of you would have SQLPS module already loaded in the PSenvironment which conflicts with the SQLServer module.

All you need to do now is remove the SQLPS module and add the new SQLServer module as shown in the below image, you can now see the new CMDLETS in the SQLServer module which you can now work with.

Ok, Now I want to select first 10 rows from my SQL Server errorlog, lets see how it can done using powershell...

Last I want to see all the errors in my errorlog last 24 hours, lets see how we can do it,

We can now monitor SQL errorlog in various ways using powershell with this new CMDLET.
In further articles we can see how we can use other CMDLETS in the new Powershell SQLServer module.

Tuesday, 21 June 2016

SQL Server 2016 SSMS

With the official release of SQL Server 2016 this month let’s try out what is new in SSMS 2016 and how we can access the new 2016 features via SSMS,

To Start with the SSMS is now a separate component and does not come with SQL DB engine install as in earlier versions.

As you can see in the above figure all the client components needs to be installed separately and does not come with the DB engine anymore, when you move on to the features page on the install it no more gives us the option of the client components.

The install of SSMS and other client components are straight forward I would not like to go into details of the install anymore. Let’s try some new features of SSMS  

1) Pin your solution screen, This is a big relief now you don’t need to search for your primary query window when you have multiple query windows are open, you can pin a query window and then work around it, the pinned window will never move out of your working space.

2) Although not entirely a SSMS concept but with the introduction of live query stats you have option to select include “Live Query Statistics” near the Include execution Plan option in SSMA(Shown in the pic below) More on Live Query statistics https://msdn.microsoft.com/en-us/library/dn831878.aspx 



3) Another new and what i think is the most interesting concept introduced in SQL Server 2016 is Query Store can be found in database properties window now, we can discuss the Query store in detail in a separate blog.(More Info:- https://msdn.microsoft.com/en-GB/library/dn817826.aspx)


4) Row level security:- RLS is another new concept for more granular security at the row level introduced in SQL 2016.(More Info:- https://msdn.microsoft.com/en-us/library/dn765131.aspx )
You can create RLS via SSMS through Databases->Security->Security Policies folder as shown below.

5) Finally you have the new Polybase concept to query non relational data stored in Hadoop or Azure Blob storage(More Info:- https://msdn.microsoft.com/en-us/library/mt143171.aspx )
You can access this via polybase folder in the SSMS as below.

In in the coming blog we will discuss each one of these new concepts in detail.

From 2016 the SQL Server developer edition has beed made free, please download and make use of it. you get all enterprise features for testing absolutely free now!!!