SSMS – Run as a different user shortcut

It’s always a good practice to have a separate account from your regular AD account (which you use to login to your workstation) when working with SQL Server, especially for performing admin related tasks. In this blog post, I will show you a simple trick how to create a shortcut for SSMS on your desktop which opens the application with different credentials.

Let’s say I am a DBA and I have two separate domain accounts, ‘SQLTREK\Sreekanth’ (Which I use to login to my Workstation/laptop) and ‘SQLTREK\Sreekanth_adm’ (Which I should be using when I connect to my SQL Servers). In this case, If I want to connect to a SQL Instance from my local SSMS installed on my laptop, what I could do is right click on SSMS(holding shift key) and select ‘Run as different user’ and provide SQLTREK\Sreekanth_adm credentials. Well, what If I don’t want to go through this process each and every time I close and re-open SSMS? Let’s see how we can achieve this.

Right click anywhere on your desktop and create a new shortcut

Now locate your SSMS.exe path on your machine and prefix with runas.exe /user:Diffuser, See below for exact syntax.

My SSMS path:
“C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe”

So, My Run as command which I should use to create shortcut is
C:\Windows\System32\runas.exe /user:SQLTREK\Sreekanth_ADM ” C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe “

This will open your SSMS.exe application as user ‘SQLTREK\Sreekanth_ADM’ instead of my regular account. Even better, if you don’t want to enter the password each time you open SSMS, you can pass /SaveCred switch. In that case, this is what I would use.

C:\Windows\System32\runas.exe /user:SQLTREK\Sreekanth_ADM /savecred ” C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe “

Now, give your shortcut a name.

Now I have the shortcut created, but it looks ugly with no icon. Well, let’s make it fancy…right click on the shortcut and go to properties and select change Icon button.

Now, click on browse and go to the folder where SSMS is installed.

In my case it’s “C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio” and select “ssms.ico” and hit open–>OK–>Apply–>OK.

Now I have my fancy looking SSMS shortcut with my admin credentials saved.

From now on I don’t have to hold shift key and select run as diff user and provide credentials each and every time I want to connect to my SQL Instances locally from my machine.

Hope this helps, Cheers!

Note: Using /savecred is a bad security practice.

Advertisements

SQL Server with Cluster Shared volumes (CSV) – Part 2

In the previous part of this series, we have seen what a cluster shared volume is and what are the advantages and other considerations to keep in mind when deploying CSVs for SQL Server workloads. In this article, I will walk though actual installation of a failover cluster Instance leveraging CSVs.

To begin with, I will walk you through my cluster setup from 20,000 foot view. I created two brand new VMs running windows server 2012 R2 and renamed them accordingly. Nothing special w.r.t disk drives at this point, Just basic VMs with a system drive(C$).

I also created 2 virtual networks in my VMWare workstation which I will be using for configuring my Public and private NICs on my nodes.

On my first node:

192.168.1.100 is the IPv4 address of my DNS server and below are my NIC settings.

Once IP address has been configured, below is how I joined my node to domain (sqltrek.local in my case).

Further reading: Deploy SQL with CSVs – Part 2

SQL Server with Cluster Shared volumes (CSV) – Part 1

Microsoft SQL Server provides us with a wide variety of solutions to architect High availability (HA) and Disaster Recovery (DR) solutions for mission-critical workloads. In this article, let’s just focus on HA, specifically Failover Clustering. Failover clustering is probably the most mature, robust and stable high availability solution which Windows Server Operating system offers. It’s been there around for few decades now and did evolve over time along with SQL Server. In this article Let’s see a hidden feature of windows server failover cluster which helps in making our already highly available SQL Server Failover clustered instances even more highly available. The new feature which we are going to talk about is Cluster Shared Volumes, AKA CSVs. Considering windows server 2019 is around the corner, I say CSVs are not a new concept in clustering, it’s been there for almost a decade now. Microsoft introduced CSVs in windows server 2008R2, but at that time SQL Server was not supported on CSVs. Well, CSVs were originally designed for Hyper-V workloads and later on enhanced for File servers and eventually landed into SQL Server beginning version 2014.

Fair enough, but why should we care about Cluster shared volumes?

Well, the idea behind introducing CSVs is to provide truly shared disks to a failover cluster which are available to all the nodes for reading and write operations. Let’s talk about a traditional Failover clustered Instance setup for a moment. During Failover, to bring SQL Server resource online, the drives should be unmounted on the previous owner and remounted on the node which will act as the primary after failover. Should your IO subsystem become bottleneck for whatever reason, Unmounting and mounting process takes longer time thus impacting the availability of the system. Whereas with CSVs, there is no unmounting and mounting of disks since they are already made available for reading and write operations across all the nodes. In other words, it reduces downtime since SQL Server resource is no longer dependent on disks to come online. Let’s talk about one more scenario where CSVs outperform traditional shared storage. Let’s assume disk(s) loses connectivity from the node which is currently running SQL Server in middle of the day, under these circumstances the cluster can leverage another path (s) available to the shared disk without having to failover the resource group to another node. This will save us from potential unplanned downtime during business hours.

How CSVs work?

Further reading : Deploy SQL Server with CSVs

Locked out of sysadmin, Now what?

In this blog post, let’s see how to regain admin access on a SQL Server Instance in case you lost it by mistake or for whatever reason. It’s not a very common scenario, but hey you never know. I ran into this some time last week(Fortunately it’s in our POC environment), Okay, Here’s the deal – we have a POC SQL Instance which was installed by an individual who is no longer working with us and apparently he forgot to make our DBA grp as sysadmins. Basically we don’t have admin rights to our own SQL Instance, SA account is disabled(Well, No one has no clue what that pwd was to begin with). So, how did we recover from this disastrous event? Before going to the details on how I fixed it, below is where I started with.

  1. Since this is a POC box, we(DBA Grp) are already members of Local Admins on the server level. Okay, that’s a good start. In case you are not local admins at windows level, You should request your Windows admins to grant you those rights(At least temporarily for the duration you regain access to SQL)
  2. I stopped SQL Server services and started(Made sure Just the DB Engine is started) in single user mode by using “-m” switch as a startup parameter.

3. Now I connected to my SQL Server instance(You can use either SSMS or SQLCMD). I will show you how to use SSMS(Run as Admin just to be sure) using the trick I showed here.

Note: when using SSMS method, don’t hit connect in your “Connect to Server” window. You will probably end up with multiple connections and yours might not be the first connection(Spoiler: Object Explorer)

I hit cancel in the above window and I selected “Database Engine Query” and created our DBA Grp login and granted sysadmin rights.

4. Once that’s done, All I had to do was go to configuration manager and remove SQL from single user mode and restart my services and Voilaa we are in as Sysadmins! If you are curios why it worked, SQL Server when started in single user mode allows any user who is a member of BUILTIN\ADMINISTRATORS group at windows level login as sql server admin. This is by design to recover form these kind of situations.

I hope you won’t get into this awkward situation, but you are probably here reading this blog post because you are dealing with this right now 🙂 Hope this helps. Cheers!

Pause and Resume Transparent Data Encryption (TDE) in SQL Server

Transparent Data Encryption (TDE) was originally introduced in SQL Server 2008 (Enterprise Edition) with a goal to protect SQL Server data at rest. In other words, the physical data and log files along with the database backup sitting on file system are protected (encrypted).

Few things to be aware of when implementing TDE:

With TDE, The data transmitted over the network is not encrypted and the data at the object level remains unencrypted. In other words, if a user has select access to a table(s) within the TDE enabled database, he/she will be able to read data with simple select statements, as the name suggests, it’s transparent. TDE does not protect FILESTREAM data and any files related to Buffer Pool Extension (BPE) are not encrypted as well, you should use file system encryption tools like windows Bit-locker or any other third party tools for this purpose. Another caveat is TDE doesn’t support Instant File Initialization for database files. Also, when TDE is enabled on a user database your tempdb database gets encrypted behind the scenes.

Okay, let’s move on to our topic. In this article, we will see how to monitor and manage TDE progress, not essentially how to setup TDE on a user database. Before moving on to our main topic, here is a quick refresher on how Transparent Data Encryption works.

Image Source

Enabling TDE on a given database is a very straightforward process. 20,000-foot view of the process is basically creating a DMK (Master DB) which is protected by Service Master Key, Cert (Master DB), DEK (User DB) and enable TDE(User DB) and you are done. But things get little tricky when you are dealing with VLDBs. What if you have a ginormous database (Let’s say a 30 TeraBytes monster) on which you have to enable TDE? Enabling TDE is not instantaneous, the SQL Server Encryption Scanner has to read all the underlying database pages and encrypt them, For a 30 TB database it might take multiple days for SQL Server to encrypt the entire database and we as DBAs should monitor the encryption progress making sure there are no side effects. In this context I am not talking about server resources, I am talking about the impact on the transaction log file when encryption scanner is in progress. So, why should we worry about LDF file when TDE scanner is in progress? Well, SQL Server doesn’t truncate the transaction log file of your database when TDE Encryption Scanner is doing its job. Things get more complicated if you are not allowed to run TDE during business hours and let’s say you have nightly ETL loads or some other scheduled job(s) which generates a considerable amount of log records. In this article, let’s see how to monitor TDE progress and how to manage transaction log when TDE scanner is in progress.

Further reading : Monitoring/Managing TDE