The Start

Nothing new or exciting to the start of my day, clean house! Now I was actually cleaning my office, not making system changes 😉 Then…. Monday happened, I mean it’s really Tuesday but it was my first day back after the weekend (The first weekend I finally didn’t have to do any system changes)… life’s good right?

I made my first talks with our DBA, and then followed up with our Developer. Our current Developer is one really hard working and amazing dev, so he needed a couple things from my to move his current project forward; a CNAME record, along with a gMSA. This is one of the many reasons I like this guy (not only understands security posture, but what is needed for it to all work!). So the first one took seconds, and the second a couple more (besides the fact I need to reboot the server, cause I choose to do IDLGA instead of granting the computer account direct permissions to retrieve the gMSA’s password)… Yes… yes I’m aware of the special klist purge command to clear kerberos tickets, but I wanted to be 100% sure. Then came the problem…

The Problem

I don’t want to get too into the nitty gritty, but the gist of it was we had an authoritative source of data that resided on an older SQL server, our Devs new project was in a whole new data center, utilizing a whole new database server.

Since we didn’t want to alter any firewall rules between the datacenters (while they are all in house and owned by the same company, the two data centers are still wall gardened off, with a 2 way trust created for most authentication purposes) this would mean either:

A) I have to allow the old SQL server to do LDAP queries against my new Datacenters DC’s. (I wasn’t in the mood for architecture changes, which I already stated so this was last resort) Then grant the new datacenters gMSA account permissions on the database.

B) Figure out a way to utilize two different accounts, to make two different source data calls, from the same App/code.

Now I like the sound of B cause lets face it, it puts all the work on the Dev and not me. (If this sounds Dilbertish…. cause it is :P) At this point I was pretty confident that this was possible… I mean… why not? Well a couple seconds later my Dev comes back and tells me that it is in fact not possible…. well sort of not possible… it’s not possible for our exact case…. for reals… let me explain, first off I’m talking about ASP.NET, second of all I’m talking about 2 different connection strings to a Database. For some references we both found, like this and this, and this, and this and even this … ok that’s a fair amount of reading (sadly I still couldn’t find one of the original sources haha) but in each case you are probably wondering (How do I specify the user name and password for an alternative connection string when using Windows Auth instead of SQL auth)…. Drum Rolll…………..

YOU CAN’T …… TADA

So…..

The Second Problem

This lead us to our second problem, while the new SQL instance was already configured for mixed auth (This means it allows Windows as well as local SQL server authentication to be permitted), our old SQL server instance…. not so much. As much as I wanted to avoid infrastructure changes, seems it was inevitable…. so I asked my DBA if this would be a problem, since you can change the auth mode on any given instance and not all instances on the server, figured this was a quick a easy solution; Enable Mixed Auth mode, re-start the instance, create a local SQL account to be hard-coded and used by the App until the source data can be properly relocated (Thus removing any hard coded garbage in the app). Alright! until….. Ughhhhhh…

The Third Problem

When my DBA went to restart the instance, he decided to use SSMS remotely (now there is nothing wrong with this, I didn’t know it was even possible and was excited to learn something new… until…) the service failed to come up successfully (Ohh boy here we go), so sure enough we get into fix mode. My DBA jumps right into Event Viewer (good man) and discovers the first error stating the service was unable to bind to the port as it is in use (DBA opens SQL Config Manager, and Services.msc and sees both services and not running). This however instantly told me one thing… the service didn’t stop properly, event though Services.msc showed no signs of running, Task Manager and Tasklist, showed otherwise. Here’s the kickers, every attempt to force stop the process (service instance sqlserver.exe) reported back “Access Denied” even running psexec (Love you Mark!!) as SYSTEM still reported “Access Denied”

The Fix

At this point I basically figured that we had to reboot the server (I also assumed it would get stuck shutting down at the “stopping service” stage, but amazingly it did not!) Sure enough after the reboot everything came up without a hitch and the new Mixed Auth mode was enabled for our Dev’s alternate ASP.NET connection string! OK I know this sounds pretty crappy for a solution but honestly it was the only thing we had left in our toolbox, and it fixed both problem one (Mixed Auth mode is now enabled for old instance) and the fact the instance came up without a problem.

Until…..

While we (DBA, Dev, and Myself(SysAdmin)) continued to test our other applications that were built via other means, it seemed a couple things were broken, this ones a little bit funny cause we assumed there was an issue for everyone, however turned out only to be an issue for the DBA and Dev, not myself (but I wasn’t on my local machine to do any front end testing from my account) so let me explain, The Dev kept digging into the real nitty gritty of the code, jumping all the way into the backend of the SQL’s stored procedures and views, to discover there was empty values being returned (Now I have no clue if this was always an issue (based on the fix) or if it actually was due to something else….anyway) turns out one of the built in views it used as a source to create a temp table was returning null thus throwing the error when calling one of the stored procedures. When the Dev and I went to talk to the DBA in the lunch room, we had discussed some of the permission changes we had just implemented on the Security Logins of the instance, and made some assumptions, so I went into the back-end AD groups to validate somethings, sure enough it was a little funny in that due to the fact their stand accounts had direct logins for the instance (generally not a fan of this, as I love scalable design and prefer to utilize IDGLA) so my DBA told me he had fixed this, and then he told me something I never would have expected and is a huge learning experience for me:

WHEN YOU GRANT AN ACCOUNT THE “SYSADMIN” ROLE/PERMISSION THE OTHER ROLES IN WHICH THE ACCOUNT IS A MEMBER DOES NOT APPLY PROPERLY (IS BYPASSED OR SOMETHING).

Literally, so what happened was there was a group we have defined to be granted sysadmin rights on the server (to manage them, not manipulate data) normally this contains admin based accounts (we all do standard account and admin accounts for least privileged best practice right :). However their admin accounts and their standard accounts were in there, which I removed, and once that was corrected and the proper nested grounds their standard accounts where suppose to get based on other roles, then applied properly and the issue was fixed.

Party in the House… Until….

Yes… believe it or not my day does not end here…. there was simply more information the great world of IT had to shove down my tiny brain that’s already overloaded and overwhelmed at the pure magnitude of knowledge you need to manage systems!! WHYYYYYY! GOD WHYYY!!!!

Anyway…. so to end the day we get a unique error message from one of our workflows, and sure enough another email from an external user providing a snipping of an error (How nice of them). Is this a coincidence…. not a chance 100% related… So again most of the heavy lifting is done by our Dev (this guy….. he’s a super star!) He managed to break it down to an assembly problem… but we were shocked as to how this could be (we checked everything was working after all our above fixes)… well until our DBA made a confession (He wasn’t happy to have found out his own account was the DB owner of a fair share of DB’s within the instance) so he secretly clean it up… well after some trial and error (reverting the change a couple times) it turned out that the error “error the server may be running out of resources or the assembly may not be trusted with permission_set external_access or unsafe” was simply due to a single missing permission needing to be granted to the new DB owner account:

In SSMS -> Instance -> Logins -> Account -> Properties -> Securables -> Check Grant for Unsafe Assemblies

Sometimes… you just gotta run unsafe code 😛

Alright! Home Time!