October 25, 2022 by andreaallred

Transactions follow me left and right but who did that over here?

I have been working my way through a fantastic training on SQL Internals and when I saw this trick, I had to write it down so I wouldn’t forget it.

Say you have a user come to you and they dropped a table sometime yesterday, but they don’t remember when and now they need it back. You could start the restore process and roll through logs until you see the drop and then restore to the hour before or you could run this super cool query to get the time the table was dropped.

(Before I ran this, I set up a test database, created a table, filled it with stuff, took a full backup and a transaction log backup, dropped the table and then took another transaction log backup)

SELECT [Current LSN]
		,[Operation]
		,[Context]
		,[Transaction ID]
		,[Description]
		,[Begin Time]
		,[Transaction SID]
FROM fn_dblog (NULL,NULL)
INNER JOIN(SELECT [Transaction ID] AS tid
FROM fn_dblog(NULL,NULL)
WHERE [Transaction Name] LIKE 'DROPOBJ%')fd ON [Transaction ID] = fd.tid

See that Begin Time? We want to roll our logs forward to right before that started. How cool is that?!!! Nearest point in time recovery that is possible all because of reading through the log to see when the drop occurred.

But this next part was the piece that blew my mind. What if I didn’t know who dropped the table, but wanted to talk to them so they didn’t do it again? I can add one more column to my query.

SELECT [Current LSN]
		,[Operation]
		,[Context]
		,[Transaction ID]
		,[Description]
		,[Begin Time]
		,[Transaction SID]
		,SUSER_SNAME ([Transaction SID]) AS WhoDidIt
FROM fn_dblog (NULL,NULL)
INNER JOIN(SELECT [Transaction ID] AS tid
FROM fn_dblog(NULL,NULL)
WHERE [Transaction Name] LIKE 'DROPOBJ%')fd ON [Transaction ID] = fd.tid

I am passing that Transaction SID into the SUSER_SNAME built in function.

Probably shouldn’t be surprised by that answer.

The song for this post is Left and Right by Charlie Puth.

April 30, 2021 by andreaallred

Only AG’s can hurt like this

While doing a planned Availability Group failover, the application stopped talking to the database. After checking the SQL Server log, we found that all the SQL Logins were failing with an “incorrect password” error. The logins were on the server, the users were in the databases, and the passwords were even right, so what was wrong? It all comes down to SID’s (Security Identifiers).

I am going to back up a little bit and point out the few main items that don’t automatically synchronize between two AG Servers. I like to think in terms of Database Level vs Server Level. If it is stored in a database and that database is in the AG, then it will synchronize. The msdb database and the master database can’t be in an AG, those two hold the agent jobs, linked servers, backup history and logins. Because a sql login can be created on two different servers, even if they have the same username and password, they can have a different SID which will make them mismatch. That is what had happened in this case.

I like to use a script when creating these logins that has the SID specified so they will match between all the AG nodes. It can be done like this:

CREATE Login MyLogin WITH password = 'SecurePassword', SID = 0x06046EG0F6C0C6488GCGG3G815EFF5GA

But how can I check if the SIDs match once they are created? I like to create a temporary linked server between the nodes to make it easier for me to verify them (it can introduce a security risk if left there). Then I run something like this:

SELECT A.name, A.sid, B.name, B.sid
FROM NodeA.master.dbo.sysusers A
LEFT JOIN NodeB.master.dbo.sysusers B ON A.name = B.name
WHERE A.sid <> B.sid

I absolutely could do this without the temporary linked server by querying each node and comparing the SID’s but this is a little faster.

This posts song is Only Love Can Hurt Like This by Paloma Faith

June 29, 2017 by andreaallred

Block the impersonating menace!

Impersonation is something that I really love in SQL Server 2014 and higher because it got so much better. It is nice to be able to impersonate a user so I can see how the permissions will work and what errors that user will see without the pain of logging in as that user. Recently I found that the impersonation feature might be a security hole in my enterprise. If you grant elevated permissions at any time to a user, your system could be at risk for someone to impersonate any login. For example, I have an end user named “Joker” that at one point had elevated permissions. We took them away, but weird things keep happening and we think it might be him. We think he is impersonating other logins like this:


EXECUTE AS LOGIN = 'Heroes\Batman'

He then will run code that he shouldn’t be able to to run.

We can fix this with a simple SQL Script. We could block Joker from impersonating Batman but then he might try Superman or Wonder Woman. We want to stop him from ever doing this again. This script will deny impersonation of any logins to a specific login. We would stop him like this:

DENY IMPERSONATE ANY LOGIN TO [Heroes\Joker]

Now our server is once again safe from any Joker that would try to harm our data.

The song for this post is Young and Menace by Fall Out Boy

March 31, 2017 by andreaallred

All the Masking in the World Can Maybe Cover Your Dirty Laundry….

I have spent the last week learning about new features in SQL Server 2016 and one that I want to play with is Dynamic Data Masking (DDM).

What is data masking? It is a way to change or hide sensitive data. If I want to hide an email address that is Batgirl@DC.com, I could either change it to be Batwoman@Heros.com using a masking software or I could use DDM to cover it like this BXXXXX@XXXXXX.com. I can also determine how many letters I want to cover with the masking in DDM.

If you want to permanently mask it for security purposes and force it to never link back to your production data, SQL Server Dynamic Data Masking (DDM) is not for you. The built-in feature only applies a mask over the top, it doesn’t actually change the data that is stored in the database. Think of SQL Servers’ version of data masking like a Halloween mask that sits on your face as opposed to plastic surgery that will forever change the way you look.

SQL Servers’ DDM will mask data to users that you set up to see the mask. This is helpful for reporting or for curious people who want to look at data they shouldn’t be viewing. It will not hide the data from privileged users. It will not protect your data from someone taking a backup and restoring it somewhere else (If you want that, try Alway Encrypted instead). As a side note, DDM and Alway Encrypted won’t work together on the same column.

Now let’s get ready to play with Dynamic Data Masking in SQL Server. (Coming next month)

Today’s song is Dirty Laundry by Carrie Underwood.

September 29, 2016 by andreaallred

Mayday this is an emergency, my linked server is using ODBC to connect to an AG…

Availability Groups (AG) and Linked servers can get really tricky. Especially if you are dealing with multi-subnet failover. The only way we have figured out how to do this is with ODBC. Here is how.

First, we have create an ODBC connection on our SQL Server. The single server in the picture below is the server we are going create the ODBC connection and the linked server on. It will go over to the AG Listener. ag-pic

First we are creating an ODBC Connection on our server that is going to link to the AG.

odbc1

Make sure to be in the System DSN section. Click Add

odbc2

We can pick either ODBC Driver 11 or 13. This is a separate driver install that we can get here. We want to install the driver and then we will see it as an option in the screen above. Click Finish.

odbc3

Give it whatever name and description wanted, but save the name for later. The server should be the name of the AG Listener. Click Next.

odbc4

Technically we don’t have to put in a login and password, but I like to test the login and password that I am going to use for the linked server. It won’t be saved here. Click Next.

odbc5

Here is the magic part, make sure to check multi-subnet failover. That is what is going to make the connection automatically fail between the two nodes. Click next, test the connection and then Finish. The ODBC connection is ready to be used by the linked server connection. Let’s build that part now.

Go into SQL Server Management Studio and under Server Objects, right click on Linked Servers and select “New Linked Server”.

odbc6

The provider needs to be OLE DB Provider for ODBC Drivers. Remember the name we gave the ODBC connection? We are going to use that here. Then go to the Security Tab.

odbc7

This is where we put in the login and it will be stored here. I also make sure this user name and password is on both Nodes of the AG with the permissions that I need. Click Server Options

odbc8

The above is what I need, but I check that I am only giving access to what is needed and not more. When we click ok, it will test our connection. If everything works with no errors, we are ready to go.

Some of the problems that we have noticed are querying tables that have big datatypes like time(3-7), timestamp, and a few others. Casting or converting the datatypes doesn’t help. If we pull the table into a view without the big datatype columns, we are able to query the view from another server, but never the base table. It has been a bit frustrating, but we are still hopeful that we can find a solution or that Microsoft with fix ODBC connections. If there is a better way to do this, please reach out to me. We have things we need to solve and could use some help.

The song for this post is Mayday by Cam.

Coming soon: Count down to PASS Summit 2016 with more pictures from PASS Summit 2015. Watch twitter and the Magic Mirrors page for more.

May 31, 2016 by andreaallred

I can make your logs clap…

The SQL error log has this nasty habit of getting big when I am not looking. There are only two ways to keep is at a normal size. One is to stop and start your SQL instance (Reboot, Restart, Stop and Start) and the other is to run this handy little script:


EXEC sp_cycle_errorlog;

This will end the current log and start a new one. Why does this matter? The SQL Error log holds information about your backups, failed logins, SQL errors, edition information and other fun stuff. The bigger it is, the longer it will take SQL to load it into memory so that you can read it. Usually when you need to read it, you are in trouble so the slower it is, the more stressed you will be.

What is a good size? I usually try to get it to roll over around 10 MB. I use a monitoring tool and when the large error log alert is triggered, I have it run sp_cycle_errorlog for me so mine always stay a healthy size. You don’t need fancy tools to do this though. If you know about how fast your logs grow, you can set up a SQL Agent job to run it on a schedule to keep your logs healthy.

How many logs should I keep? This is completely up to you, but since I keep my logs so small, I try to keep 15 of them. Why so many? I do it so I can go back and see issues further back if needed. You can adjust the amount you keep by right clicking on SQL Server Logs in SSMS and selecting “Configure”

Super cool, but what about the Agent error logs? There is a script for them as well!


USE msdb;
GO
EXEC sp_cycle_agent_errorlog;

See? Healthy and Happy Logs! Your Logs will be clapping with joy.

March 1, 2016 by andreaallred

I need to know that I got you with me, SQL don’t reboot on me…

Did you know that you can change the password on the SQL Service account that is running your SQL instance without a reboot or restart? Turns out this is true. We have a new round of password requirements and it means that we need to change passwords on servers more often. But, since we need our servers up and reboots have to be heavily planned, we needed a solution that kept us from having to restart an instance after a password change. This lovely msdn article explains all the details, but let me give you the cliffs notes.

First, standard warning: Don’t do this in production until you have tested it over and over and are completely comfortable with how it will work in your environment.

Now to the fun part: Go to SQL Server Configuration Manager, then SQL Server Services and right click on the service on which you will be changing your password. Select Properties.

Here you are going to update the password and confirm the password and click OK.

Guess what? That’s it! On a stand alone instance the new password will take effect immediately and as the msdn article highlights, clusters get tricky.

Happy Server Securing!

July 28, 2015 by andreaallred

And you said you are unconsolable…clean up before you leave

Greetings,

I am approaching my last day at my current job. I love it here and will be really sad to leave, but have an awesome opportunity to grow my knowledge and career with a company on my “want to work for” list.

There are a lot of things to take care of before I leave. I have been updating documentation (with meme’s) so that it is useful and fun. I am trying to wrap up all my tickets and outstanding items and last night I woke up and realized, I was the owner of some databases. This is how I fixed it:

I launched a query window on my Central Management Server to save time, but you can run this on one server at a time if you want. I used the syntax from sp_helpdb to find out what I wanted to query:

select name, isnull(suser_sname(sid),'~~UNKNOWN~~') AS Owner, convert(nvarchar(11), crdate),dbid, cmptlevel
from master.dbo.sysdatabases
WHERE suser_sname(sid) = 'domain\MyUserName'

Some of the applications in my environment run under a special user and I didn’t want to interfere with those, I just wanted to fix the ones that use me. Then I borrowed some code from Brent Ozar:

SELECT 'ALTER AUTHORIZATION ON DATABASE:: ['+ name +'] to sa;'
FROM master.dbo.sysdatabases
WHERE suser_sname(sid) = 'domain\MyUserName'

Here’s one I run on the CMS to find any SQL Agent Jobs that I own across my enterprise and then I can run the update scripts that are generated on the individual servers.


SELECT 'EXEC MSDB.dbo.sp_update_job ' + char(13) +
'@job_name = ' + char(39) + [Name] + char(39) + ',' + char(13) +
'@owner_login_name = ' + char(39) + 'sa' + char(39) + char(13) + char(13)+';'
FROM msdb.dbo.sysjobs
WHERE SUSER_SNAME(owner_sid) = 'domain\MyUserName'

February 10, 2015 by andreaallred

Ima make a deal with the SA so the SA don’t bite no more

T-SQL Tuesday

TSQL Tuesday #63 – How do you manage security?

Yay, so this is my first official blog party post. I was late with my post last month because I didn’t have it figured out until I saw everyone posting and wondered what was going on.

One of my goals for this year is to increase security across my 50 SQL servers. The SA log-in is a dangerous one and I don’t want it out there on my servers, the trouble is that it is hard to exterminate. Here is my new plan.

I am going to script out my SA log-in and password using this script.

Next I am going to rename the SA log-in to something else and give it a much stronger password. This information will go into my password safe so that I have it just in case I need it.

I am going to create a dummy SA. I have many vendor databases that claim they need SA, but they really don’t and I don’t want them to have high privileges. I am going to work on lowering the privileges to only give what they need (this will be a slow server by server process to get the permissions right). The reason I am keeping a log in with the name “SA” is because of vendors who hard code that user name.

As I was talking to people in the #SLCSQL user group last night, someone suggested that we can also monitor that dummy SA login to watch for attacks. It is a great idea and plan to include monitoring on the new log in.

Lars Rasmussen suggested I have a user on each server that will always be there to handle running jobs and other database needs. I plan to include this too so that when I don’t have a proper SA log in, I will still have a log in that can handle all my fun stuff.

By doing all of this, my SA won’t bite me anymore.

T-SQL Tuesday is a blog party started by Adam Machanic (b|t) just over five years ago. This month it is hosted by Kenneth Fisher (b|t).

Andrea Allred presents RoyalSQL

Bringing happy endings to all your data stories.

Category Archives: Security