DBA Tips/Tricks – MSSQLTREK

Configure/Manage Error logs – SQL Server on Linux.

How to configure and manage SQL Server Error logs in Linux environment:

In this blog post let’s see how to configure and manage SQL Server error logs on an Instance running in Linux environment. I am sure most of you are already recycling (re-init) your error logs (exec sp_cycle_errorlog) on regular basis by scheduling a job and controlling the number of logs as needed on SQL@Windows. Well, If you are not doing so, please go ahead and do yourself a favor…configure your error logs. By default on SQL@windows, it keeps 6 archives (128 in SQL@Linux). Imagine where your SQL got restarted multiple times(let’s say 6 times) in short duration for whatever reason, Oops! your precious logs are already gone. See below screenshots on how to do that for SQL@windows,
Using object explorer in SSMS:


Using T-SQL:
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 20
GO

Now let’s move on to SQL@Linux. If you notice the above T-SQL code, SSMS is basically updating windows registry values behind the scenes which isn’t possible in Linux. (Registry is an exclusive windows OS thing…Duh…..!) See below screenshot, you don’t even get configure option in SSMS under SQL Server logs (BTW, Did you notice the tiny Penguin!).

On my lab server with default settings, You can notice that I have 82 error logs as of now.(By Def, max 128 in SQL@Linux and 6 in SQL@Windows)!

Let’s say I want to set it to 20, what I have to do is, use mssql-conf utility and specify the desired number.

sudo /opt/mssql/bin/mssql-conf set errorlog.numerrorlogs 20

The changes will take effect after the service restart and you can validate by reading the contents of mssql.conf file as shown below.

Note: Old error log files should be manually removed or moved as needed.

Hope this helps, Happy Monday!

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.

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.

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)
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!