SQL Server, PASS, and other data mishaps
Posts tagged SQL Server
SQL Active Directory User ID SID Mismapings
Feb 17th
Sometimes login mapping issues exist where you least expect them
This is not the traditional SQL login SID mismap issue that is frequently encountered and discussed here. This mismap was a new one on me so I thought id document it.
When trying to add a new Windows login for SQL Server 2005 or 2008 (probably earlier versions but not tested) you may wind up with this error message
Msg 15025, Level 16, State 2, Line 1
The server principal 'DXXXX\UXXX' already exists.
Contrary to the error message, If we need to proove the account doesnt exist, the following code should suffice
select name from sys.server_principals where name = ‘DXXXX\UXXX’
SQL wont let you create an account because it thinks it already exists, but clearly the account doesnt exist.
How did this happen?
If a login was created previously for a windows account that has since been renamed in the active directory you cannot grant the new userid access to the SQL server because the SID already exists in SQL and you cannot duplicate it. This occurs when renaming an account in Active directory, because the SID is reused instead of recreated.
Find the pre-existing SID
The SID from Active directory can be obtained many ways, the one I use is PsGetsid which can be obtained here the syntax you’d use for this is
PsGetSid DOMAIN\USER (or DXXXX\UXXX from earlier)
This will return the SID in the SDDL Format of S-x-x-x-x
Unfortunately SQL Stores the binary representation of the SID not the SDDL format. You have several choices to do the mapping, you can hand figure out the SDDL mappings from sys.server_principals using this page Or the better way -> Todd Engen had found some need to convert this before and designed a great function to do this conversion from binary to SDDL , im only going to list it here for completeness, the original is found here
CREATE FUNCTION fn_SIDToString
(
@BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN
IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)
DECLARE @StringSID VARCHAR(100)
DECLARE @i AS INT
DECLARE @j AS INT
SELECT @StringSID = 'S-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
SELECT @StringSID = @StringSID + '-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))
SET @j = 9
SET @i = LEN(@BinSID)
WHILE @j < @i
BEGIN
DECLARE @val BINARY(4)
SELECT @val = SUBSTRING(@BinSID, @j, 4)
SELECT @StringSID = @StringSID + '-'
+ CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
SET @j = @j + 4
END
RETURN ( @StringSID )
END
After creating this function use it like so
select name from sys.server_principals where dbo.fn_SIDToString(sid)=‘S-X-X-X-X’ where the ‘S-X-X-X-X’ is the SID obtained earlier from PsGetSid
This should return the name of the user that has the offending “duplicate” SID
Once you know the logon ID that has been renamed you can script out the permissions of that ID (hopefully it doesn’t own any objects), drop it, and recreate it with the appropriate name and grant any additional permissions
How did I get here?
Jan 27th
There’s a meme going around that I thought I’d take my turn at answering.
Better late than never I suppose, Work always seems to have a way of getting in the way of posts like this!
It all started with a CAT3 cable
It all started on a dark night in the middle 90′s, I was enrolled in college sitting in my dorm room trying to connect my brand spanking new Pentium 133mhz computer to our college network so I could partake in what was at that time a huge LAN group playing Warcraft/Diablo/Duke Nukem. The problem was no one on the campus apparently knew how to connect to the network, yes it was a smallish campus. The only piece of guidance that could be found was in the welcome doc. “Network connectivity can be established in the bookstore” after contacting the bookstore and procuring the required 10baseT network card (~175$) they basically said, take this wire and plug it in the wall, everything else will work automatically. Well, even today we know things rarely work that easily. The cable that was sold to me by the bookstore was a regular phone cable because apparently the bookstore managers didn’t know any better, it wasn’t their fault though since the public campus network was less than a year old at that point. Somehow I spent enough time trying to get the correct table that I was lucky enough to get hooked up with the “campus nerd” who happened to live in the dorm 1 floor above me. He set me straight, told me where to get the required cable and handed me a scribbled list with the required connection info. Many late nights and much tinkering later I was successfully connected. Being the natural tinkerer I shortly figured out all about the network and what it took to get win 3.1 and 95 connected. Shortly, I became the “campus nerd” and when it was apparent to me that I was naturally inclined with computers, and not so much with coursework I wasnt inerested in, I quickly gave up school and began bartering computer work.
Then there was a book
A short while later I had landed a job as an all around network guy. I was doing everything and anything for a relatively small business. One day my boss proudly announced we were going to be getting a new server with a Database (SQL 6.5)! Apparently we had outgrown our existing business systems and the decision had been made to install what was essentially a combined financial/payroll system. A few short months later, in the middle of a payroll processing cycle our SQL server decided to do what SQL 6.5 did quite often, it got corrupted. Since I had a grand total of 4 months experience in SQL a consultant was called in and she fixed our problem. More importantly she brought with her a copy of the latest and greatest SQL book and as luck would have it, she left it behind. For the next 6 months I studied that book inside and out. A “database geek” was born
Finally, a chance meeting
In 2004 I was attending my first precon (given by Kimberly Tripp) at my first PASS Summit when I was looking for some lunch and happened to sit with 2 guys, Pat Wright and Tom Larock that are to this day two of my closest PASS friends. There is little doubt that the experience of meeting these 2 and attending the volunteer “roundup” lead by Wayne Snyder has had a profound impact on my career (this blog is a testament to that impact). A “volunteer geek” was born. Being a volunteer for PASS and participating in the SQL Server community has taken my skills up at least 2 notches, for that I am thankful.
These are the technical moments of my life that led me here, since I’m nearly the last one to answer this, I thought id go ahead and tag my friend Pat Wright since I noticed he hadn’t answered yet. Otherwise, I have enjoyed reading everyone else’s paths to a very similar outcome!
Photo Courtesy of Darren Hester
How do you do Disaster Recovery
Jan 27th
Going through the process of a large scale multi-location disaster recovery made me stop and think about all the different incarnations that can be used to recover database servers.
Living with a datacenter in Hurricane alley, We’ve been doing disaster preparedness(recovery) on a small scale for many years but this year we’ve been working towards recovering all of our assets to an offsite colocation. That part of the decision is easy, the actual method used to do these recoveries is definitely up in the air and I fully expect our processes to change for the better, every time we redo our disaster testing (many times a year going forward).
In exploring the recovery process we quickly realized that our “hardware failure” recovery documents weren’t going to work effectively in a datacenter failure situation. So, it was time to design a new set of criteria for success. I thought Id share our thought process and how we plan on tackling this always fun experience. Its worth mentioning in a side note that no SQL replication is wanted/allowed for in our case.
1st thought: Bring up blank OS builds for the database servers, load SQL Server, Patch it to the correct level while the tape restores of the database backups are happening, Recover the system databases then kick off the individual restores(that are scripted with the regular nightly backup jobs)
- Benefits to DBA: clean, repeatable, documentable process that we are mostly in control of.
- Drawbacks: Time consuming, potential version match issues, recovering system databases is always “fun”
2nd thought: Use a windows snapshot to restore the OS/Sql Binaries and Sql System databases then recover the user databases using the aforementioned scripts. This also buys us the nicety of having litespeed already installed
- Benefits to DBA: Faster, System level recovery done in a standard (for our system group) method
- Drawbacks: system/SQL recovery out of our (DBA) control
Since our Systems engineers are already asking to go the snap route (because thats common for other application servers), and we expect this method to take less overall time, we are planning on trying that first. Depending on how that test goes, we will likely have option 1 as a backup plan or potentially try that next time thats why we’re testing it, so that we can make sure we have it right.
As always, there’s more than 1 way to accomplish the same outcome so my question is how do you do off-site disaster recovery (testing)? Or maybe the better question is do you do disaster recovery testing? If not why?
Using Aliases with SQL Server
Jan 21st
Even an old dog can learn new tricks
I had an Aha! moment recently. For my entire career as a DBA I have generally considered aliases for connections a workaround for bad behaving applications. Whenever someone said “alias” my mind immediately heads to SQL Server client configuration aliases which I try to avoid if at all possible (since they are configured on each client) It never snapped to me until recently that DNS aliases may be a good solution to a few problems we’re currently experiencing.
For disaster recovery reasons, as well as for manageability reasons we have decided to start using DNS aliases for every application connection to database servers. This should allow us to have the luxury of moving databases from server to server without having to reconfigure multiple applications which would normally be a whole process in itself since the code was already migrated to production which is locked.
Using DNS aliases should also allow us to swap highly important applications over individually to a remote datacenter, which could have less computing power, without having to switch every application and thus kill the performance of that standby server.
There are certainly limitations to this, if for instance you want to move applications from one named instance to another. In our current environment this isnt much of an issue since many of our production instances are indeed default. The other major limitation to this is any change will have a small amount of downtime while the DNS changes are propgated throughout the network.
One more “gotcha” that weve already run into is vendor applications (surprise surprise) that resolve the DNS name to an ip address and then store that inside the application configuration.
We decided on working out a naming standard that looks like this:
For direct database access, where an application only connects to 1 database the following is used
dsDBNameEnvName
i.e. dsCustomerProd
for an application (like sharepoint) where many databases are going to be accessed we change it a bit
dsAppNameEnvName
i.e. dsSharepointProd
While this isnt necessarily a “new” idea, it was certainly a different idea in our environment and I suspect there are other “DBA’s” out there like myself that dont have a habit for using our network skills on a regular basis
Photo courtesy: Ronn Ashore
A new blog is born
Nov 20th
A new take on an old idea
As if there aren’t already enough blogs around about SQL server I’ve decided to start and build my own. For those of you tortured lucky enough to know me you’ll know exactly how hard the decision has been to start this blog.
I hope this blog will become a place that I can throw random thoughts about with almost reckless abandon. I plan on using this place as a sounding board for ideas as well as a place to store my ideas for use at a later time.
I will likely be focusing more on SQLPASS than on SQL Server troubleshooting itself so, this is where this blog will be different from most. I think SQLPASS could be so much more than it currently is and I intend to do my level best to make that happen
Oh, super huge thanks go out to Brent Ozar (twitter, web) for his awesome how to setup a blog series. Without that, I would not have this site today. I owe you a beer at the next SQLPASS Summit!
So, welcome to my world, please feel free to look around (theres nothing else here)
