Ryan Adams Blog

SQL, Active Directory, Scripting

Discovery

I ran across an issue where I had just configured database mail on a new server, went to send a test email, and I waited to receive it.  I waited…and waited…and waited, but still nothing.  I started to investigate and verified that database mail was indeed enabled, Service Broker was running, and the mail queue was started and running.  I went to go look at the sysmail_allitems view to see the status of my test emails to find that the sent_status column showed them as unsent.  The only errors I could find were these:

Log: SQL Server Agent
Message
[474] Unable to refresh Database Mail profile Server Mail. (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database.
System.Data.SqlClient.SqlException: profile name is not valid at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.Td)

Log: SQL Server Agent
Message
[260] Unable to start mail session.

I spent the better part of a day trying to figure this out.  Those error messages were not exactly helpful in pointing to the root cause.  Since this was a new server that I did not build myself nor did I install SQL myself I decided to uninstall and reinstall SQL.  When I ran the install it failed and upon investigation of the install log file I found an error that pointed me toward the issue.

Error: 17182, Severity: 16, State: 1.
2016-03-23 14:49:21.08 spid12s     TDSSNIClient initialization failed with error 0x80090331, status code 0x80. Reason: Unable to initialize SSL support. The client and server cannot communicate, because they do not possess a common algorithm.

At this point I knew I had a protocol issue.  The thing to note here is that this is actually blocking the install of SQL Server and I’ll explain why that is in just a minute.

Why

You may recall something called the POODLE attack that revealed a vulnerability in SSL 3.0 and TLS 1.0.  This particular server had SSL 3.0, TLS 1.0, and TLS 1.1 disabled in the registry.  Also note that TLS 1.2 was NOT disabled.  The server was running Windows 2012 R2.  These protocols were disabled to prevent the possibility of a POODLE attack.  If you are wondering how to disable these protocols on your servers then look at Microsoft Security Advisory 3009008.  To disable them for the whole OS scroll down to the Suggested Actions section and look under the heading “Disable SSL 3.0 in Windows For Server Software”.

I also want to note that the PCI Security Standards Council pushed back the date for getting off of SSL and TLS 1.0 to June 30th, 2018.  In addition to that, it should also be noted that Microsoft’s Schannel implementation of TLS 1.0 is patched against all known vulnerabilities.

You might be wondering how SQL Server was originally installed if having the protocols disabled prevented my subsequent installation attempt.  The reason is that the registry changes to disable them was made after the original install of SQL Server.  I’m glad this error happened on my subsequent install or I might not have ever figured this out.

Database Mail Breaks

So far the only issue I have run across by disabling these protocols (post installation) is that Database Mail can’t send emails and they just queue up with only the above vague messages.  The one that says it is unable to refresh a profile is an error and the other one is a warning in the SQLAgent log.  Most people don’t have alerts raised for this and if you sent those via Database Mail, you’ll never get them anyway.  This is where I want everyone to be cautious.  If these protocols have been disabled (and you may not be aware of it) then you will not get alerts for things like failed SQLAgent jobs that could be backing up your DBs or Hard IO errors, etc…  That means you could have issues on your SQL Server and might not even know about it, because the email notification is just sitting in the queue.

The Cause

The reason this issue exists is because Database Mail does not yet use the .Net Framework 4.6.1.  That is the newest version of the Framework and the only one that supports TLS 1.1 and TLS 1.2 for SMTP.  The .NET framework version 3.5 SP1 and earlier did not provide support for applications to use TLS 1.2 as a cryptographic protocol. In the update below they enabled the usage of TLS 1.2 in the .NET Framework 3.5 SP1.

The Fix

Microsoft could have fixed this in two different ways.  The first is that Database Mail be changed to utilize the .Net Framework 4.6.1 if it is installed.  The second is that the old versions of the .Net Framework be changed to support TLS 1.2.  As mentioned above, Microsoft chose option number two and just released a fix.  Check out THIS ARTICLE to obtain the fix.  The article will help you decide if you are running a vulnerable version or not and obtain the correct fix if needed.

Let me start off with a disclaimer that these thoughts on the January PASS board meeting are my own as I do not speak on behalf of PASS.

I have been on the PASS Board of Directors for about 4 months now and we had our first in person board meeting in late January. You can go here to read the January meeting minutes.

Since then, it has been a blur of quick changes as we had a few unexpected ones.  Thomas LaRock chose not to run in the PASS Executive elections in 2015 (blog) and therefore  became the Immediate Past President.  Adam Jorgensen was elected to the President role.  James Rowland-Jones (JRJ) was elected to fill the EVP seat, which would begin in January 2016; however, he later had to resign when he accepted a position with Microsoft.  JRJ’s resignation left the remainder of JRJ’s two-year term as a PASS Board Director vacant immediately.  To fill that spot, the board needed to appoint someone to finish out the Director-at-Large term.  Argenis Fernandez was appointed and voted in by the board last December.

Things appeared to be settled and Adam had assigned portfolios. I was assigned the Programs portfolio, which largely consists of our annual Summit conference.  As for the EVP vacancy in the ExecCo, Grant Fritchey was voted in as the replacement, which he assumed January 1.  This left the remainder of his seat open, meaning the board had to appoint another person and voted on Allen White during the January Board meeting. It’s extremely unusual to have this much change in such a short amount of time.

Adam took this opportunity to make some portfolio changes that worked out extremely well because it put all of us in portfolios that we were suited to best. Argenis was moved to the SQLSaturday portfolio, which was an excellent fit for someone that knows these events well from multiple viewpoints. I was moved to the Chapters portfolio. I’ve been on the board of the North Texas SQL Server User Group for the last five years and a regional mentor for the last four, so this was a perfect fit for me, especially since this area is my passion. Lastly, Allen was assigned the Programs portfolio. I personally couldn’t be more excited to have him in this role and his experience in PASS and the industry will most certainly give this portfolio great guidance. I know some of the things he is thinking about and I can say that Programs has a bright future.

So what is coming up in the future and what plans are being made? Normally in the past every portfolio owner would present their goals and plans for the coming year at the January board meeting.   Adam, since taking on the role as President, is taking the time to look strategically at PASS’s priorities and setting a long term vision.   Each portfolio director will work with the Exec to ensure that our goals frame the organization’s strategic priorities.  As a newly elected Director, this gave me a time advantage which is helpful since a couple of my goals are a little lofty.  It gives me time to do the research and discover the probability of success with those goals. My hope was to share those with you in this post, but since they are still a little fluid you’ll have to remain in suspense. I will say that the goals are in support of increasing communication and resources, marketing, and enhancing the Regional Mentor (RM) program.

There are a lot of exciting things coming and tons of amazing ideas from the board and that just energizes me more. One of the biggest takeaways I had from the meeting was the passion of the folks currently on the board. Every idea and every decision is poured over painstakingly and that has never been more clear to me than after these board meetings.  No one is perfect and we might not get it right every time, but I can assure you that these folks always do their due diligence. Being on the board is not easy, and I have more respect for that and the folks in that room now than I already had.

I have 2 things I would like to ask of the community.

1.)  Every year at the PASS Summit I make it a point to thank every single person serving on the board for their time, commitment, and willingness to make difficult decisions that support the best interest of PASS. My first challenge for you is to do the same and thank those that have had an impact on you or the community. We are a completely volunteer-run organization and surprisingly, we don’t do this nearly enough.  A simple “Thank You!” goes a long way and encourages us all.

2.)  My second request is that if you have suggestions or changes you would like to see for Chapters or the Regional Mentor program, please send me an email. I might not be able to fix everything or accommodate every request, but I can certainly try or at the very least set the path in motion. I was invited to an RM call recently for the North Central region and I walked away with a bunch of notes, so know that my door is always open.

 

Have you ever had the need to query Active Directory right out of SQL Server using native TSQL?  There are 3 ways to accomplish this.  The first is CLR, but I won’t be covering that method.  The other two methods are OPENROWSET and OPENQUERY.  OPENROWSET requires Ad Hoc Distributed Queries to be enabled and OPENQUERY requires a linked server.  I had a fellow MVP ask me how to do this recently, but his restriction was that he could not use linked servers.  He will need to use the OPENROWSET method to avoid linked servers, but I also challenged him to consider creating the linked server, running the OPENQUERY, and then deleting it.  You can do all that in 1 piece of code and I’ll show that below.  It’s just an option worth knowing in case it fits for your environment.

In the case of my friend, they used something random for user names, but he wanted to get the friendly name.  In Active Directory the attribute he was looking at is called the sAMAccountName, but the attribute he needed was the displayName.  My examples below will only pull the displayName attribute, but you can easily add in any attribute you are looking for.  If you are not sure what attribute you are looking for or what is even available, then I suggest you download and take a look at the Softerra LDAP Browser.  It is also useful for learning how to write LDAP queries.  The last thing to be aware of is that what you see in Active Directory Users and Computers is generally not the real attribute name or it is not spelled exactly the same when referencing it programmatically via an LDAP query.  Below are some screen shots of those attributes we are dealing with in this article and their real name in AD.  You can reference this article on translating what you see in ADUC to LDAP or the real AD name.

Attributes in ADUC

Query Active Directory DisplayName
Query Active Directory SAM and UPN

In the code below, the first thing we do is enable Ad Hoc Distributed Queries so we can try out the OPENROWSET method.  The advantage to this method is not having a linked server and being able to call it directly out of TSQL.  Once we have that enabled we write our query and you’ll notice that we are essentially doing 2 queries.  The first query is the LDAP query inside the OPENROWSET function.  Once those results are returned we are using another query to get what we want from the result set.  Here is where I want you to stop and think about things.  If my LDAP query pulls back 50 attributes, or “columns” in SQL terms, and I tell it I only want 10 of them, what did I just do?  I brought back a ton of extra data over the wire for no reason because I’m not planning to use it.  What we should see here is that the columns on both SELECT statements are the same.  They do not, however, have to be in the same order.  The reason for that is because LDAP does not guarantee to return results in the same order every time.  The attribute or “column” order in your first SELECT statement determines the order of your final result set.  This gives you the opportunity to alias anything if you need to.

The second part of the code looks at using OPENQUERY.  The first thing we do is create a linked server using the Active Directory provider called ADsDSOObject.  Next we write our OPENQUERY and you’ll notice that it looks very similar to OPENROWSET, but instead of defining the connection provider in the function call we reference the linked server.  The same rule applies here about how many attributes you pull back and how many you actually plan on using, so continue to keep that in mind.  The last thing I do is delete the linked server.

Query Active Directory


--There are 2 ways to query AD from SQL Server.  The first is using OPENROWSET and the second is using OPENQUERY which requires a linked server.

/*** OPENROWSET METHOD ***/
--You have to enable Ad Hoc Distributed Queries to use OPENROWSET.  Note the OPENQUERY does NOT require this to be enabled since it uses Linked servers.

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

SELECT DisplayName
FROM OPENROWSET('ADSDSOOBJECT','adsdatasource','SELECT displayName
FROM ''LDAP://mydomainFQDN.com/ou=mySubOU,ou=myTopOU,dc=mychilddomain,dc=myTLdomain,dc=com''
WHERE objectClass = ''User'' ')

/*** OPENQUERY METHOD ***/
--Here is where we create our Linked Server connection to AD
EXEC master.dbo.sp_addlinkedserver @server = N'AD', @srvproduct=N'Active Directory Services Interface', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AD', @locallogin = NULL , @useself = N'True'
GO

--Here is the query using the above created Linked server
SELECT displayName FROM OpenQuery (AD,
'SELECT displayName
FROM ''LDAP://mydomainFQDN.com/ou=mySubOU,ou=myTopOU,dc=mychilddomain,dc=myTLdomain,dc=com''
WHERE objectClass = ''User'' ')
GO

--Here we delete our Linked Server
EXEC master.dbo.sp_dropserver @server=N'AD', @droplogins='droplogins'
GO

 

More Active Directory and SQL Related Articles

 

Yesterday Microsoft announced that they will now support SQL Server on Linux.  They are targeting mid-2017, but you can download a preview now.  I am on a technical list where I saw someone inquire about the support for Windows authentication or if SQL authentication would be the only choice.  Those of you that know me remember that Active Directory is my second strongest skill set next to SQL Server.  What you might not know is that I went through a Linux phase and up until a couple years ago I always had a Linux machine sitting next to my Windows machine.  It appears it may be time for me to get back into this world.

Back to our question at hand.  Since the preview just got released I have not had a chance to test this out with SQL Server.  However, you can use Active Directory accounts with Linux and Unix by using Samba and PAM.  I see no reason why this implementation would not also work with SQL Server on Linux, but again I have not yet had a chance to test this out.  Since I already have some written installation instructions, that’s what I am providing here.

SQL Server on Linux Authentication with Samba

    • Make sure to remove any installed binary Samba packages to start clean
      • rpm -qa | grep samba
      • rpm -e SambaPackageName
      • Do the above command for each samba package found.  If it complains about dependencies then run the command like the following
        • rpm -e – -nodeps SambaPackageName
    • Download latest Samba source code
    • Extract the source code
      • tar xvfz samba-latest.tar.gz
    • Run the configure script from the source directory to create a make file
      • ./configure –with-msdfs –with-smbwrapper –with-smbmount –with-ldap –with-ads –with-cifsmount –with-libsmbclient –with-winbind –with-krb5 –with-pam –with-acl-support 2>&1 | tee config.my.log
    • Run make from the source directory
      • make 2>&1 | tee make.my.log
    • Run make install from the source directory
      • make install 2>&1 | tee makeinstall.my.log
    • Copy the following files to your documents directory for future reference
      • config.my.log
      • make.my.log
      • makeinstall.my.log
      • config.status
        • This file contains the options that you ran the configure script with
        • Search for “with options” to find the line with your configure options
    • Add Samba executable directory to path for root and yourself
      • Add the following to the end of the PATH statement in /root/.bash_profile and /home/myuser/.bash_profile
        • :/usr/local/samba/bin:/usr/local/samba/sbin
    • Add Samba MAN pages directory to the MAN path
      • Edit /etc/man.config and add the following two lines to the end
        • MANPATH /usr/local/samba/man
        • MANPATH_MAP /usr/local/samba/bin /usr/local/samba/man
    • Copy /usr/local/samba/sbin/mount.cifs to /sbin so that you can mount with CIFS
    • Make sure to create a smb.conf file in /usr/local/samba/lib BEFORE you start Samba
      • A sample can be copied from the examples folder in the downloaded source code
      • run testparm on the smb.conf to verify correct syntax
    • To automatically start Samba upon boot
      • create a file named startsmb in /usr/local/samba/sbin with the following contents
        • #!/bin/sh
        • /usr/local/samba/sbin/smbd -D
        • /usr/local/samba/sbin/nmbd -D
        • /usr/local/samba/sbin/winbindd
      • Make it executable with chmod +x startsmb
      • Add the following line to /etc/rc.local to execute the above script upon boot
        • /usr/local/samba/sbin/startsmb
    • Edit /etc/resolv.conf and make sure it has appropriate entries for “search” and “nameserver”
      • search mydomain.com
      • nameserver 192.168.0.xxx
      • nameserver 192.168.0.xxx
    • Configure Kerberos for ADS realm and KDC
      • Edit /etc/krb5.conf and add the following to the [libdefaults] section
        • default_tgs_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • default_tkt_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • preferred_enctypes = RC4-HMAC DES-CBC-MD5 DES-CBC-CRC
        • default_realm = MYDOMAIN.COM
        • dns_lookup_kdc = true
    • Join Samba server to the domain as a domain member
      • net ads join -U username
    • Look for and delete the following files if they exist
      • /lib/libnss_winbind.so
      • /lib/libnss_winbind.so.2
      • /lib/libnss_wins.so
      • /lib/libnss_wins.so.2
      • /lib/security/pam_winbind.so
      • /lib/security/pam_winbind.so.2
    • Copy your newly compiled winbind, wins, and pam libraries from the source
      • cp sourcecodepath/source/nsswitch/libnss_winbind.so /lib
        • chown root /lib/libnss_winbind.so
        • chgrp root /lib/libnss_winbind.so
        • chmod 755 /lib/libnss_winbind.so
      • cp sourcecodepath/source/nsswitch/libnss_wins.so /lib
        • chown root /lib/libnss_wins.so
        • chgrp root /lib/libnss_wins.so
        • chmod 755 /lib/libnss_wins.so
      • cp sourcecodepath/source/bin/pam_winbind.so /lib/security
        • chown root /lib/security/pam_winbind.so
        • chgrp root /lib/security/pam_winbind.so
        • chmod 755 /lib/security/pam_winbind.so
    • Create a symbolic link for your libnss_winbind and libnss_wins libraries
      • execute /sbin/ldconfig
      • verify that libnss_winbind.so.2 and libnss_wins.so.2 exist in the /lib directory
    • Modify /etc/nsswitch.conf to make the lines for passwd and group look like this
      • passwd: files winbind
      • group:  files winbind
    • Add the following lines to your smb.conf file in the global section
      • idmap uid = 10000-20000
      • idmap gid = 10000-20000
      • winbind enum users = no
      • winbind enum groups = no
      • winbind use default domain = yes
      • winbind nested groups = yes
      • allow trusted domains = no
      • template homedir = /home/%D/%U
      • template shell = /bin/bash
    • Stop and restart the SMBD NMDB and WINBIND processes.  You can restart them and ensure the starting order is correct by executing the /usr/local/samba/sbin/startsmb file you created earlier.

Configure PAM

  • Configure PAM to use Winbind for authentication on the local system if desired
    • Edit /etc/pam.d/system-auth and add the following lines
      • auth             sufficient    pam_winbind.so use_first_pass
      • account        sufficient    pam_winbind.so use_first_pass
      • password     sufficient    pam_winbind.so use_first_pass
      • session         required    pam_winbind.so use_first_pass
  • The finished file should look like the following
    • #%PAM-1.0
    • # This file is auto-generated.
    • # User changes will be destroyed the next time authconfig is run.
    • auth        required      pam_env.so
    • auth        sufficient    pam_unix.so nullok try_first_pass
    • auth        sufficient    pam_winbind.so use_first_pass
    • auth        requisite     pam_succeed_if.so uid >= 500 quiet
    • auth        required      pam_deny.so
    • account     required      pam_unix.so
    • account     sufficient    pam_succeed_if.so uid < 500 quiet
    • account     sufficient    pam_winbind.so use_first_pass
    • account     required      pam_permit.so
    • password    requisite     pam_cracklib.so try_first_pass retry=3
    • password    sufficient    pam_unix.so md5 shadow nullok try_first_pass use_authtok
    • password    sufficient    pam_winbind.so use_first_pass
    • password    required      pam_deny.so
    • session     optional      pam_keyinit.so revoke
    • session     required      pam_limits.so
    • session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
    • session     required      pam_unix.so
    • session     required      pam_winbind.so use_first_pass

Network Binding Order Warning

One of the more common errors I see when setting up a Windows cluster (usually in preparation for a SQL Server Failover Cluster Instance or a SQL Server Availability Group) is regarding an incorrect network binding order.  You will see this presented as a warning in the Cluster Validation Report.  There are actually two different errors that I have seen with the first being very common and the second being more rare.

Error 1

Rule “Network binding order” generated a warning.

The domain network is not the first bound network.  This will cause domain operations to run slowly and can cause timeouts that result in failures.  Use the Windows network advanced configuration to change the binding order.

What this error means is that the network card used to connect to your domain network is not at the top of the network binding order.  The fix for this is usually pretty easy because we just need to go into Control Panel…Network and Internet…Network Connections and make the change.  Once we get there we need to identify which NIC card is connected to the domain network and that can be seen in the “Network Category” column shown in the screen shot below.  You’ll notice that I have labeled my connections Public and Private and they both show “Domain Network”.  If you are configuring a multi-subnet cluster you will see the exact same thing, but if your cluster is on a single subnet the Private network connection will show “Public Network”.  So in a single subnet it’s the one labeled “Domain Network” that you are targeting and in a multi-subnet cluster it’s your Public connection you are targeting.

GUI Network Binding Order

In order to change the network binding order we need to go into the advanced settings.  Starting in Windows 2008 this option is hidden.  If you don’t see it hit ALT on your keyboard and the Advanced option pointed out in the previous screen shot will appear.  We need to select that and then go to Advanced Settings.  You will now be presented with the Advanced Settings box shown below.  In the screen shot you will see that my Public network is the second in the binding order and we need to move it to the top by selecting it and hitting the up arrow.  Click OK and go run Cluster validation again to see if it is resolved.

Network Binding Order

There is a chance that either your domain network was already at the top of the binding order, or you ran Cluster Validation again and it failed with the same error.  If that is the case then you either have a ghost network card (can be caused by NIC changes or driver changes) or the Microsoft Failover Cluster Virtual Adapter is bound before the domain network.  These adapters are not show in the GUI, but can be found in the registry and other places like the ipconfig /all command.

Unfortunately the network binding order in the registry uses GUIDs instead of friendly names, so we’ll have to do some translating to find and move the domain network to the top.  The first thing we will do is go figure out what the GUID of the domain network NIC is by running the following command from a command prompt.


WMIC Nicconfig Get Description, SettingID

You’ll remember that I renamed my NIC cards to be called Public and Private, but that’s the friendly name and not what will be returned from WMIC.  WMIC returns what is in the “Device Name” column from the very first screen shot above.  In my case it is called “Intel(R) PRO/1000 MT Desktop Adapter”.  You can see this pointed out in the screen shot below where we can see the output of WMIC in the command window.  Note that the GUID starts with A7.

Registry Network Binding Order

Now we just need to open Regedit and head to HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage\Bind.  That’s right, we’re getting down and dirty now so make sure you know what you are doing and above all else backup your registry before you make any changes.  Mistakes in the registry can be costly and destroy a system so proceed with caution because from here on out the responsibility lies solely with you…not me.  In the screen shot above you can see that my Public Domain Network is next to the last in the list and we need it to be at the top.  As an aside, I have also pointed out where the Microsoft Failover Cluster Virtual Adapter is located since I see this listed above the Public network from time to time.

The fix here is to cut the GUID for the Public Domain Network that starts with A7 and paste it at the top of the list.  Now we can go run Cluster Validation and life should be good unless you get the second error we’ll talk about now.

Error 2

Note that the error message is the same error you got above.  However, it’s a completely different issue.  So let’s say you verified the above and that the domain network is the first in the list, but the error persists.  Go Look in the following file and search for “IsDomainInCorrectBindOrder” to find the warning in the log file.


C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\"Newest Log File Folder By Date"\Detail.txt

Here is a sample of the section you are looking for.

Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.NetworkBindingFacet
NetworkBindingFacet: Looking up network binding order.
NetworkBindingFacet: Network: ‘Production Team’ Device: ‘\Device\{0BF4D354-E6E9-480C-91CF-DC598282C4C1}’ Domain: ‘LITWARE.COM’ Adapter Id: ‘{0BF4D354-E6E9-480C-91CF-DC598282C4C1}’
NetworkBindingFacet: Network: ‘Local Area Connection’ Device: ‘\Device\{4DB91193-72F1-4713-A938-EB73F27CFEC8}’ Domain: ” Adapter Id: ‘{4DB91193-72F1-4713-A938-EB73F27CFEC8}’
NetworkBindingFacet: Network: ‘Heart Beat’ Device: ‘\Device\{5AC63784-8088-40F7-93C8-37F9CD03D445}’ Domain: ” Adapter Id: ‘{5AC63784-8088-40F7-93C8-37F9CD03D445}’
NetworkBindingFacet: Network: ‘BackUp Network’ Device: ‘\Device\{52AEDCB0-9E8E-4243-9D5D-ED86E602DF23}’ Domain: ” Adapter Id: ‘{52AEDCB0-9E8E-4243-9D5D-ED86E602DF23}’
IsDomainInCorrectBindOrder: The top network interface ‘Production Team’ is bound to domain ‘LITWARE.COM’ and the current domain is ‘CONTOSO.COM’.
Evaluating rule : IsDomainNetworkTopOfBindings
Rule running on machine: Server1
Rule evaluation done : Warning
Rule evaluation message: The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced configuration to change the binding order.
Send result to channel: RulesEngineNotificationChannel

The issue here is that the server is joined to the LITWARE.COM domain, but the current domain is that of the currently logged in user which happens to be CONTOSO.COM.  Another way to say this is that the server is joined to the LITWARE.COM domain, but you logged with a user account from the CONTOSO.COM domain to create the cluster.  From a domain perspective these are completely different domains that have been trusted and it’s possible that they are in different forests too, but again they are trusted.  Technically this configuration is correct as the public/domain joined network is indeed at the top of the list.  You have two choices here.  You can safely ignore this warning or you can log out and back in with a user in the LITWARE.COM domain.

If you are looking for more information on Clustering, AGs, or FCIs you can Find More Posts Here.

Let’s say you have a port conflict and need to change the port on your Availability Group endpoint.  How can we accomplish this?

The first step is to change the port on the endpoint itself.  Note that as soon as you do this the replica on which you are running it will no longer be able to replicate data until you have updated the URL as well.  Here is the code to change the port, just make sure you know what the IP is unless you are changing that too.  If you are, then read this post on changing the endpoint IP address.

ALTER ENDPOINT [MyEndpoint]

STATE = STARTED

AS TCP (LISTENER_PORT = 7777, LISTENER_IP = (10.x.x.x))

FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = Windows Negotiate, ENCRYPTION = REQUIRED ALGORITHM AES)

GO

 

The second step is to change the replica URL.  This part is pretty straight forward since you already know the port from above.  What I want to encourage you to do is use the full URL or FQDN for the server name.


ALTER AVAILABILITY GROUP [MyAG]

MODIFY REPLICA ON 'MyNode1' WITH (ENDPOINT_URL = 'TCP://MyNode1.mydomain.com:7777')

GO

That’s all there is to it!

I had someone email me and ask how they could change the IP address on their Availability Group Endpoint.  It’s no surprise that IPs need to be changed from time to time due to certain circumstances, but what you might be wondering is where during the creation of your AG did you assign the endpoint an IP.  If you used the GUI to create your AG then the answer is that you never assigned your endpoint an IP.  So why do you even care?

The one thing I challenge folks to think about when I teach my HA/DR precons and AG sessions is to think about the types and amount of traffic across the entire ecosystem.  An Availability Group is going to have cluster heart beat traffic, user traffic, SQL replica traffic, and depending on your storage you might have traffic for that as well.  Your network is just like plumbing.  You can only fit so much water through a 1 inch pipe at a time.  The same goes for network connections, and that’s a lot of traffic to put over a single NIC port.  However, you can separate all of those traffic types and changing the IP of the endpoint will let you give the AG data replica traffic its own network pipe.

Every replica in the AG has its own endpoint so you will need a new IP for each one.  Go assign the IPs to a NIC card in each server.  Now we need to change the endpoint to use the new IP on each replica.  The good news here is that no outage is required to do this.  Here is the code to change your endpoint.  You just need to edit the endpoint, port (Read the caution below), and IP.

ALTER ENDPOINT [MyEndpoint]

STATE = STARTED

AS TCP (LISTENER_PORT = 5023, LISTENER_IP = (10.x.x.x))

FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = Windows Negotiate, ENCRYPTION = REQUIRED ALGORITHM AES)

GO

Caution!

You MUST use the same port you are using today. If you decide to change the port then the URL will also need to be updated. You can read this post on how to do that.

Have you ever seen the error below in your SQL Server log shortly after startup?  You’ll actually see two of them and you can see the difference between them in the screen shot, but here is the text.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/Node1.stars.com:1433 ] for the SQL Server Service.  Windows return code: 0x2098, state: 15.  Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos.  This is an informational message.  Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

Kerb Error in Log

So what causes this error and how can you fix it?  The first thing to note is that it is an informational message and not actually an error.  There are two scenarios in which you will see this message.

  1. The first scenario is what you see right out of the box.  SQL Server does not have the rights on a domain service account to register Service Principal Names (SPNs).  The description we see here is very clear in telling us that SQL Server could not register the required SPNs.  It even tells us the exact SPN syntax it attempted to register.  If you want to use Kerberos you have to register the SPNs manually or give the service account the right to perform the change itself.  If you decided to register them manually then now is a good time to write down the SPNs from the description.
  2. The second scenario is a weird one that throws people off.  If you choose to manually register the SPNs on the service account and restart SQL Server, you’ll still see the same message in the log.  Now why in the world would this message even show up if you already registered the SPNs?  In fact, many folks will see this message and assume they are not using Kerberos, because the message clearly states that it could not register the SPNs.  The assumption is usually that they got the SPN syntax wrong or that the SPNs never got registered.

Just for kicks, let’s jump back over to my test server and take a look at the current connections.  Most folks will add a WHERE clause to the following query to just look at their current connection, but I’m going to caution you about that.  If you’re on the server itself you won’t get accurate results because you end up using Shared Memory unless it’s disabled.  We are looking to see if there are any Kerberos connections at all so we don’t want to filter the result set.

SELECT * FROM sys.dm_exec_connections
--WHERE session_id = @@SPID

Kerb Connection

Well that’s an interesting result, huh?  I clearly have Kerberos connections despite the message I keep getting in the SQL Server log.  So why is that?  Well it comes down to the semantics of the message.  The message said it couldn’t register the SPNs and that’s true.  It couldn’t register them because you already did it.  So if you ever see this message, make sure you go look at your connections first (using the above query) to see if it is something you need to address or not.  If you see KERBEROS in the auth_scheme column then you are all set.

If you want the message to go away completely, there is only one way to do that.  You have to give the account running the SQL Server service the permissions to change its own SPNs.  You can do that by opening the properties of the account and heading to the security tab.  You will find an account in the account list called SELF.  Grant that account “Write to Public Information”, restart the SQL Server service, and the message will disappear.  Now you’ll see a new message stating that SQL Server was able to successfully register the required SPNs.

If you are looking for more information around Kerberos and SQL Server you can find more right here on my blog at http://www.ryanjadams.com/category/kerberos/ .

I ran across a bug in SSMS 2016 CTP 2 back in August.  If you try to right click and look at the properties of a 2014 availability group using the SSMS 2016 CTP you’ll get an error.  The bug had already been submitted by my friend Trayce Jordan, so I just added some comments on my specific version (13.0.400.91) where I saw the issue.  You can find that Connect Item Here, but you’ll notice that is has been closed and marked as fixed.

I’m not sure why it was marked as fixed, because the issue still exists in version 13.0.600.65 and 13.0.800.111 and there are no comments at all about the purported resolution.  Here is the error text and what it looks like.

Cannot read property BasicAvailabilityGroup. This property is not available on SQL Server 2014. (Microsoft.sqlserver.Smo)

AG Property Error

You’ll notice that it refers to something called a Basic Availability Group.  That’s a new feature being added in SQL Server 2016 which essentially mimics Mirroring.  Although the feature has been in the CTP and “Public”, it has been under NDA until the last several weeks.  I’ll be blogging about this next month after the holidays.

Back to the task at hand.  Since this issue was originally reported in CTP 2 (and closed as fixed without comments) I opened a new Connect Item on it today to report it still existing in CTP 3.  If you support availability groups then I suggest you take the time to go up vote it.

VOTE HERE

 

SQLSaturday Austin

I’ll be doing a precon for SQLSaturday #461 in Austin on January 29th, 2016 titled “Creating a High Availability and Disaster Recovery Plan” .  If you are in the area or thinking of attending this event, I would love to have you in the class.  We are going to cover backups, windows clustering, AlwaysOn Failover Cluster Instances, AlwaysOn Availability Groups, and more.  This class will take each of those technologies in a progressive order to build on each other.  At the end of the day we will have a single solution built out in virtual machines on my laptop that use all of those technologies to build a comprehensive and real world high availability and disaster recovery plan.

Still not convinced?  Here are some testimonials from previous classes.

Dallas Attendee

I just wanted to say thanks again for the training!  I took the 70-462(Administering SQL Server) exam yesterday and got all of the High Availability questions correct.  Your training was very thorough and I really appreciate it!

Oklahoma City Attendee

This is the best class I have attended in my entire life!

Denver Attendee

This past weekend was SQL Saturday #331 Denver. I always enjoy SQL Saturdays, but I was especially looking forward to this weekend because I was going to attend a SQL Saturday Pre-Con for the first time! The pre-con was fantastic! The Denver SQL Server User Group did an outstanding job, and it was a wonderful day of learning and networking.

I attended Ryan Adams’ session A Day of High Availability and Disaster Recovery. Ryan did an excellent job with this all-day session. You could tell that his agenda was carefully planned to build upon each module. Ryan’s first module was an in-depth tour of backups and restores, pointing out that these concepts were foundational to all High Availability (HA) and Disaster Recovery (DR) solutions. In his next module, Ryan expanded on backup/restores as he introduced us to mirroring. Ryan then walked us through the concept of Windows Server Failover Clustering (WSFC). Culminating in his module on AlwaysOn Failover Cluster Instances and Availability Groups, Ryan tied all the concepts together providing a holistic view of HA/DR. I would definitely recommend this all-day session to anyone looking to expand his or her knowledge of HA/DR. To find out more about Ryan Adams, you should check out his blog at ryanjadams.com.

Madison Attendee

One thing I liked the most about this session is the speed of the presenter was just right.  He would get our feedback frequently to see how fast or slow we want to move.  He had a lot of real world examples from his own experience and from other participants.

Madison Attendee

Great lecture!  Ryan is a very logical presenter; made the material extremely easy to understand.  I greatly enjoyed the class.

Want the full description?  Here it is:

Abstract: You have been tasked to configure a high availability and/or disaster recovery plan to ensure your company’s systems are always up and running while able to withstand a disaster. SQL Server offers several options, but how do you choose the right one to meet your business requirements? I’ve been faced with this same problem. I’ll help you figure out how to choose the right technology. We will start off with a solid foundation in backups and demonstrate how to configure them for initializing the various HA/DR technologies while also cutting your backup and restore time in half. You will see how you can keep an offsite copy of your database in sync with your production system with mirroring.

You’ve read that several of the HA/DR solutions require Windows Failover Clustering, but you don’t know anything about managing a complicated Windows cluster. I’ll show you how to setup and configure a Windows cluster and we’ll build on that with SQL Server AlwaysOn Failover Clustering Instances that provide the 99.999% of up-time your management demands. Lastly we’ll dive into how to setup AlwaysOn Availability Groups to solve both HA and DR in one solution. We will see how all these technologies work together by discussing a case study and developing a comprehensive solution just like you have to do when you return to the office. Here’s what you will learn:

Planning a backup strategy to avoid a data disaster
Cutting your backup and restore time in half when initializing DBs for Mirroring or Availability Groups
Configuring mirroring to keep production in real-time sync with an offsite database copy
Learning configuration tips to increase throughput
Configuring a Windows Failover Cluster to meet that five 9s business requirement
Discovering how to properly configure a Windows Cluster quorum to support the AlwaysOn feature set
Implementing a SQL AlwaysOn Failover Cluster Instance
Creating a SQL AlwaysOn Availability Group to solve both your HA and DR requirements in one solution

You’re just as excited and convinced as these fine folks, right? Then click the link below to get signed up.

Eventbrite_Button