SQL Server Logins across Availability Groups and Orphaned Users

Recently I ran into an issue regarding AlwaysOn availability groups and SQL Logins; Anytime I would failover the AG to a secondary node, I would get an orphaned user. I know that orphaned users are caused by a mismatch of SIDs. So the SID from the Primary database for the SQL User isn't the SID from the secondary database.

I had to put my thinking hat on for this one. I knew that I needed to find a way to match SIDs across the Availability Groups for the failover to work without having to fix the orphaned user. And I discovered that by using T-SQL to create the user, you can specify what SID to use (thanks to http://sqlserverpedia.com/wiki/Moving_SQL_Server_Logins_Between_Servers).

So here are the steps to get SIDs uniform across the board:
1. Create the user on the Primary Node (No need to recreate if already created)
2. Run the following query on the primary node:
      SELECT sid FROM sys.server_principals WHERE name = 'SQLUserLogin'
3. Using the SID from the output, create the following query (where SID = the result from step 2):
      CREATE LOGIN [SQLUserLogin] WITH PASSWORD=N'Password', SID = 0x0000000000000000000000000;
4. Run the query from step 3 on all secondary nodes of the Availability Group.
5. You now have to go and specify any properties of the Login on the secondary nodes. Examples are password enforcement and user mappings.
6. You should be good to go!

Note: You cannot use Alter Login to change the SID on an already created user. http://msdn.microsoft.com/en-us/library/ms176060.aspx

Failed to Create Availability Group, SQL Server error 41042 encountered

Wow, it's been close to six months since my last post!!! Where has the time gone?

Anyway, I ran into an issue that no amount of Google-fu seemed to help me with. The issue was "Microsoft SQL Server, Error: 41042". I ran into this problem trying to create an Availability Group using the name of an AG that had been previously deleted, but appears to have not been completely removed. Here's a screengrab for the error:

As stated, this was an AG name for an AG that had existed and was gone from SQL Server.... but hadn't disappeared from the "Failover Cluster Manager." So I deleted the AG's service manually from the Failover Cluster manager.  

This appeared to do the trick, but then I got the error. So what to do????? As I mentioned, I checked google profusely to no avail. And after thinking for a bit, decided that this had to be a registry issue. I did some googling to determine where SQL Server Availability Group Registry keys live, and found the following excerpt from http://msdn.microsoft.com/en-us/library/ff929171.aspx:

In the registry I navigated to HKLM\Cluster\HADRAgNameToIDMap, and found the AG name I was trying to use. I deleted the key, and reran the AG wizard and.... still no luck. So I decided to do this on every node that had been a part of the AG and... Voila!!!! IT WORKED!!!


Now remember, use this at your own risk.

Availability Group Listener's IP address keeps changing between Online and Offline address

I've been testing Multi-Subnet Availability Groups and I ran into a peculiar situation which I hadn't experienced before; the IP address kept flipping between the offline and online IP address for the resource.

I was a little perplexed, I had tested Multi-Subnet AGs on our old Colocation, and was now testing it with our new (and improved) colo. I tried lots of things, but it came down to one cluster resource setting for my AG... RegisterAllProvidersIP.

When this setting is set to 1, any IP address that is associated with the clustered resource. As shown below:

This means that dns will return either IP address, and your client could fail connecting if the offline IP address is returned by DNS after the TTL timer expires.

By setting RegisterAllProvidersIP to 0, only the online IP address is registered. Success!!

version of SQL Server instance MSSQLSERVER does not match the version expected by the SQL Server update

While trying to patch SQL Server 2008 R2 SP1 CU4 with CU6, I got the following prompt

It specifically says the below:
"The version of SQL Server instance MSSQLSERVER does not match the version expected by the SQL Server update. The installed SQL Server product version is 10.50.1600.1, and the expected SQL Server version is 10.51.2500.0"


This error says that I don't have SQL Server SP1 installed. This is rather interesting as I know for a fact I have SP1 installed. What to do?!!

Well I decided to reapply SP1 and see if there was a repair option. There wasnt a repair option, but on the config screen, I saw it had "Incompletely Installed" as the Upgrade Status. I ran it, and it quickly completed.

After the install completed, I retried to apply the latest CU patch, and voila!! It worked!!.

Ports needed to connect to remote SQL Server Instance

I've been working with our System Center Configuration Manager Engineer in getting his Configuration Manager Distribution Points working after connecting to a Configuration Central Management Point. Everything has been working fine, except our Development Environment Distribution Point cannot connect to our Central Management Point (CMS) database.

We've been at it for a week. The CMS db is located on a SQL Server 2008 R2 named instance in our Production Environment, and the Distribution point is located in our development environment.

The following works when trying to connect using SSMS:
Server
Server\Instance,port

Does not work:
server\instance

This was driving me nuts as the distribution point uses server\instance to connect. And according to our Config Manager Engineer, there isnt a way to specify... which may or may not be true. So I did my due diligence and discovered that on a named instance, if using server\instance as your connection string, then the requesting server goes through UDP port 1434 to request connection variable data regarding your sql named instances.

We opened UPD port 1434, and voila. It works!!

Blog that helped me figure it out:
http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

Installing Microsoft Dynamics AX 2012 on SQL Server 2012

With SQL Server 2012's release this past April, we knew we would have to prepare for our future deployment of Microsoft Dynamics 2012 to run on it. We had successfully upgraded existing versions of SQL Server 2008 R2 supporting Dynamics 2012 to SQL Server 2012. But one thing we never tested was installing Dynamics 2012 directly onto SQL Server 2012.

I set up all the pre-requisites for the Dynamics AX. The DB successfully installed and then I got the following error on the AOS install:
An error occurred during setup of Application Object Server (AOS) MicrosoftDynamicsAX at port 2712.
The following error/warning occurred:Cannot find the object 'UTILIDELEMENTS', because it does not exist or you do not have permission. Cannot find the object 'UTILELEMENTS', because it does not exist or you do not have permission. Cannot find the object 'UTILMODELS', because it does not exist or you do not have permission.
Hmmm.... interesting as I know we've successfully gotten these running on SQL Server 2012 before. I did a bit of google-fu and saw someone listing as needing to run Dynamics AX 2012 patch listed as KB2680186. Luckily we had this in our share. I ran that update, and noticed that it didnt actually update a client, but rather installed an update of the "Installation Support Files".

Once that completed, I rebooted my server and voila. It installed.
-Victor


Edit: I had used version 6.0 of the Dynamics AX 2012 installer. Although I was able to proceed, I got various errors that I couldnt get past. I ended up undoing the AOS install and db install.

I then redid the AOS and DB install with version 6.1, which was patched to run with SQL Server 2012 out of the box, and lo and behold, IT WORKED!!!!!

Drop User Failed - User has granted one or more permission(s). - Part 2

Once again, I ran into the same issue as my previous post on a different server. Trying to drop a user, but not able to because "User has granted one or more permission(s)."

previous post:
http://becomingexceptional.blogspot.com/2012/04/drop-user-failed-user-has-granted-one.html

This time around though, there was one caveat, the endpoint was in use. I ran the same steps to determine what the endpoint was and what it was named.

So I had to change endpoint ownership to our SQL service account. But how to do it? I googled alter endpoint owner and found the following technet article:
http://technet.microsoft.com/en-us/library/ms187359.aspx

ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO 'domain\svcaccount';
GO

I ran it, and I was now able to successfully delete the user.