DBA Tips/Tricks, SQL Server 2017

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!

Advertisements
SQL Server 2017, SSMS

SSMS – Security Vulnerability Assessment(VA)

In this blog post, let’s explore one of the fantastic features added to our good old friend SSMS by Microsoft, Security Vulnerability tool. I am currently running SSMS 17.6, but this feature which we are going to see has been introduced in version 17.4. Okay….So, let’s see what this is all about.

I have a database named “ChickenNuggets” which I would like to know what are the potential security risks it has. Well, All I need to do is connect to SQL Instance from SSMS 17.4 or above and Right click on the database and go to tasks and navigate to VA and do a scan. See below screenshot for what I am talking about.

Give it a path and Wait for few seconds and voila…..you get something like this.

As you can see, it reported 10 failed checks for this DB. Also, You can select any item and approve as baseline if that’s normal in your environment, That way SSMS doesn’t include that particular item under failed list as it has been included in baseline.

As you can see it’s giving a detailed explanation along with what exact Query it ran for this particular check and the best part is it gives you remediation script as well.

Now, What If I want to scan something at Instance level, I could perform the same on Master Database.

That’s all good so far right? But there is still huge room for improvement. What if my instance has 100 databases? I hope you already guessed where I am going with this….Yes, That’s correct, I have to repeat this 100 times at individual database level. Well that sucks. Also, I don’t see anyway to export these results to different formats. It would be really nice if Microsoft gave us an option to export this to PDF format or HTML.  But hey…this is a start and I am positive Microsoft is listening to community and these will be made available to us very soon in future releases of SSMS.

BTW only SQL Server 2012 Instances and later are supported, and of course Azure SQL Database 🙂 .This is extremely light weight tool which you can run at any time without impacting your workload. It just runs bunch of DMVs…Shouldn’t block anything. But hey, don’t trust anyone and anything posted online when it comes to your Production Databases. After all that’s our Bread and Butter!

Happy weekend folks.Cheers!

AlwaysOn Availability Groups, SQL Server 2017

AOAG – SSMS 17.4…Surprise!

Howdy folks! So, I was installing SQL Server 2017 on my new Cluster lab setup and found something interesting with SSMS 17.4 when creating AG. Before going there, let me show you guys what has been changed with SQL 2017 setup.

Note: This is not a step by step setup walk through.

As you can see there is no option to install SSMS/SSRS from original SQL media.

Well, so I downloaded the latest and greatest version of SSMS as of today(which is V17.4).

Note: On my server, SQL DB Engine installation was completed in 3 minutes, where as it took about 17 minutes to Install SSMS!!

Well, now I was all set to create an Availability Group(from SSMS) and found something interesting in the wizard(It has a Read-Only Routing tab 🙂 ). Not sure when this was introduced, but initial releases of SSMS 2017 didn’t had this IIRC.

If you are curious, Prefer secondaries is the default backup setting.

So, below is the screenshot of how it looks like if we navigate to ROR tab. We can setup load balancing as well right here from this wizard which is an awesome addition IMHO.

We are not going to create routing lists here in this blog post, I will show you how to do that using GUI in one of my future posts.

Linux/RHEL/CentOS, SQL In Linux, SQL Server 2017

Managing SQL Server Services in Linux Environment

In the previous blog post we have seen how to Install SQL Server on Linux(CENTOS). In this post, let’s see how to manage SQL Server Services in Linux environment. After all as a DBA, we should know how to manage our database services 🙂 Let’s get started…

In Linux world, to manage services, we’ll be using command called “SYSTEMCTL“. We can check the status, stop,start,disable,enable any service running on a linux box using that command.

Syntax is very simple to remember: systemctl action ServiceName. Check this documentation for more details.
https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/System_Administrators_Guide/sect-Managing_Services_with_systemd-Services.html

Verify the current status of SQL Server services:

Syntax: systemctl status mssql-server

As you can see SQL Server Service is enabled and is running. Okay, let’s stop and disable them.

Stop and Disable SQL Server services:

Syntax: sudo systemctl stop mssql-server
sudo systemctl disable mssql-server
Once this is done, check the status of SQL services(See below screenshot for all the three steps)

Now, Let’s start SQL services(Since we have disabled the services as well besides stopping, let’s enable and then start SQL)

Enable and Start SQL Server Services:

Syntax: sudo systemctl enable mssql-server
sudo systemctl start mssql-server

It successfully created syslink right after enabling the service. Then I started SQL service, Let’s check the status of the service again.

Happy SQLing on Linux 🙂 Fun Fun….

Linux/RHEL/CentOS, SQL In Linux, SQL Server 2017

Microsoft Loves Linux – Installing SQL Server on Linux

In this blog post, let’s see how to Install SQL Server on Linux. It’s been a while since Microsoft announced SQL on Linux, but I thought of waiting for a while before I set this thing in my lab. With the release of SQL Server 2017 CTP 2.0, It’s about time. Okay ,so let’s get started!

Note: I’ve Centos installed in my lab. That’s where I started learning/exploring Linux. I am installing SQL Server on CentOS (Which is exactly same as RHEL). Every single command shown in this blog post should work on RHEL as well.

Step 1: Connect to Linux machine and download config/repo file using CURL command and verify if it downloaded the file.

As you can see in the above screenshot, I logged in as root user. It’s not required, in fact not recommended to ever login as root user to perform any sort of activities on linux box.(Well, I guess It’s okay sometimes If you are Linux admin and/or you absolutely understand what you are doing 🙂 ). You can just make use of sudo instead. ( As long as you are listed in sudoers file or a member of wheel group, you should be able to use sudo)

Step 2: Now we know that the file exists, lets try installing SQL using YUM Package.  I logged out of root and I am using my regular account “Sreekanth”. See below, It didn’t let me Install SQL because I didn’t have rights to use sudo.

As I mentioned above, I will be adding “Sreekanth” to wheel group by logging in as root again.

Now…logging in as “Sreekanth”, my regular user account, Let’s try it again…

Well, It works. If you notice, it says “Run sudo /opt/mssql/bin/mssql-conf setup to complete setup”…Okay let’s do that. That’s our next step.

Step 3:

This is the step where we accept license terms, set “sa” pwd …all that good stuff.

Done. That’s it guys! I am really impressed how easy it was actually to install SQL Server on Linux. It’s actually way faster than installing SQL on windows box. This entire process took less than 3 minutes for me! Wooohoooo!

Let’s check few other things before wrapping up. Let’s check the status of SQL Server service on our Linux box.

Okay…that confirms SQL service is actually Up and Running. Now, let’s open port 1433 in firewall.

Step 4: Let’s connect to this sql server instance from another client machine. (connect to IP address using the sa account which we provisioned earlier)

I see Agent XPs are disabled. Okay…let’s turn it on.

Give it a shot setting up in your own test area. Trust me, This reaaaaally feels good once you have SQL Server running on your Linux box 🙂  That’s it folks for today! Hope this blog post helps…Later.