Endpoints: 3 Great Queries to Test AG Endpoints

TSQL Tuesday

Time flies, seasons are flying by and seemingly changing day by day. We have slippery slopes here, there, and everywhere. Every now and again Database professionals need a quick moment to get away from the fray. As luck would have it, it is now time for a fabulous party – to get away from all of the reality the world is offering us these days. So, without further ado, let’s escape reality for a brief moment and party on with TSQL Tuesday as we delve into the validation of Endpoints!

This party, that was started by Adam Machanic, has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

Invitation

This month, John McCormack (b | t) invites us to share our most powerful and biggest tools. Well, maybe that is just a half truth. While it may be valid, the actual invite is for each party-goer to share a story about their favorite handy go-to “short” scripts. Granted, “short” is a protected class and is really more of a perspective from the eye of the beholder. What is short for you may be long for somebody else (e.g. maybe some of you think a short script is anything less than 2000 lines of code).

Another take on the term “short”, could be that it takes just a short amount of time to pull out a saved script to perform the task at hand (some routine task you may perform but doesn’t quite rise to the level of automation such as what Aaron Bertrand shared here). Please go and check the invite from John – here.

John has given us an outstanding topic this month. This is a critical key to success for a DBA in my opinion. Every DBA really should have some sort of cache of saved “quick” / “short” scripts and every DBA should be able to pound out a quick three line script for quick info without too much thought. I must confess that I am not alone in this line of thinking.

Check out all of these topics from the community from past TSQL Tuesday challenges – here (along with some of my offerings: Essential Tools, XE Power Tools, and a litany of others. The moral of that sentiment is that a quality DBA should have a cache of tools to make him/her better at what they do!

Validate your Endpoints

After building an Availability Group in SQL Server, I like to run some validation checks in order to build out the Endpoints. I like to do this because I have run into issues in the past. Unfortunately, I have discovered that I still run into those issues (no matter what method is used to build out the Availability Group) if I don’t run these validation tests. The tests are rather simple. After doing it a few times, I combined them into a single script that I can pull out of my source control very quickly (thus still sort of meeting both types of “short” I described earlier in this post).

I will share the initial short scripts I had previously used to perform these validations and then conclude by sharing what the final script looks like that combines them all into a simple easy to run script for your toolbelt.

Validate Endpoints Exist

Symptom: Unable to add additional nodes to AG and buttons are greyed out in the GUI.

Checking for a missing endpoint (or any of the preceding symptoms) is rather simple which then leads to a very easy fix. We need to see if the endpoint is created. Even though you may have specified all of this information when setting up the AG and the AG does get created on the primary node, the Endpoint doesn’t create on the primary node and thus prevents the addition of any other nodes to this AG.

USE master;
GO

SELECT SUSER_NAME(principal_id) AS EndpointOwner
		, name AS EndpointName
	FROM sys.database_mirroring_endpoints;

USE master;

SELECT SUSER_NAME(principal_id) AS EndpointOwner

, name AS EndpointName

FROM sys.database_mirroring_endpoints;

The Fix…

That is a pretty simple short script to check if the AG endpoint is present. If the script returns nothing, then it is time to go and create the endpoint. You could do it with another simple short script such as the following.

CREATE ENDPOINT Hadr_Endpoint  --default endpoint name for an AG endpoint
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   --5022 is the default port
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO

CREATE ENDPOINT Hadr_Endpoint --default endpoint name for an AG endpoint

STATE=STARTED

AS TCP (LISTENER_PORT=5022) --5022 is the default port

FOR DATABASE_MIRRORING (ROLE=ALL);

Validate Endpoints Owner

After the endpoint is created, you will want to check the owner of the endpoint. There should be no surprise here that the owner of the endpoint is the principal that was used to create the endpoint. This may be suitable in some environments, but is definitely not acceptable in many environments. And thus, if the endpoint owner is your principal, then you should change it.

Validation Script

To validate the owner, you can simply re-run the script I posted in the previous section – reposted here for simplicity.

USE master;
GO

SELECT SUSER_NAME(principal_id) AS EndpointOwner
		, name AS EndpointName
	FROM sys.database_mirroring_endpoints;

USE master;

SELECT SUSER_NAME(principal_id) AS EndpointOwner

, name AS EndpointName

FROM sys.database_mirroring_endpoints;

The fix…

And then you can run the following to change the owner of that endpoint if it doesn’t quite match what you desire. Personally, I prefer to change the endpoint owners to ‘sa’.

USE master;
GO

SELECT SUSER_NAME(principal_id) AS EndpointOwner
		, name AS EndpointName
	FROM sys.database_mirroring_endpoints;

USE master;
GO
Declare @SQL varchar(2048);

SELECT @SQL = 'ALTER AUTHORIZATION ON ENDPOINT::' + dme.[name] + ' TO sa;'
    FROM sys.database_mirroring_endpoints dme
    WHERE dme.type_desc = 'DATABASE_MIRRORING';

PRINT @SQL
--EXECUTE (@SQL) --uncomment if you wish to apply the changes and the script looks correct

GO

SELECT SUSER_NAME(principal_id) AS EndpointOwner
		, name AS EndpointName
	FROM sys.database_mirroring_endpoints;
GO

USE master;

SELECT SUSER_NAME(principal_id) AS EndpointOwner

, name AS EndpointName

FROM sys.database_mirroring_endpoints;

USE master;

Declare @SQL varchar(2048);

SELECT @SQL = 'ALTER AUTHORIZATION ON ENDPOINT::' + dme.[name] + ' TO sa;'

FROM sys.database_mirroring_endpoints dme

WHERE dme.type_desc = 'DATABASE_MIRRORING';

PRINT @SQL

--EXECUTE (@SQL) --uncomment if you wish to apply the changes and the script looks correct

SELECT SUSER_NAME(principal_id) AS EndpointOwner

, name AS EndpointName

FROM sys.database_mirroring_endpoints;

So far so good, right? These are easy short scripts that anybody could use to validate their Availability Group endpoints. Let’s keep going and take a look at the third validation.

Validate Endpoints Permissions

The next validation I perform is due to errors that pop in the error log with the following text:

Database Mirroring login attempt by user ‘domain\user.’ failed with error: ‘Connection handshake failed. The login ‘domain\user’ does not have CONNECT permission on the endpoint. State 84.’.

This error message has most of the pertinent information that can help you figure out what is causing this error to be thrown. You would think this should always be applied properly when the endpoint is created for the Availability Group. Alas, sometimes it doesn’t get properly applied so we have to take additional manual steps.

When you see this error message, you will typically see that the service account is the account that is missing the connect permission. In addition, I typically see this when using a managed service account (or group managed service account). To verify the connect permission is properly applied, you can run this validation script.

Validation Script

DECLARE @SQL VARCHAR(1024),
        @SQLSvcAccount VARCHAR(128);

SELECT @SQLSvcAccount = dss.service_account
FROM sys.dm_server_services dss
WHERE dss.servicename NOT LIKE 'SQL Server Agent%';

SELECT ep.endpoint_id,
       p.class_desc,
       p.permission_name,
       ep.name AS EndpointName,
       sp.name AS Grantee,
       ep.type_desc,
       ca.service_account
FROM sys.server_permissions p
    INNER JOIN sys.endpoints ep
        ON p.major_id = ep.endpoint_id
    INNER JOIN sys.database_mirroring_endpoints dme
        ON ep.endpoint_id = dme.endpoint_id
    INNER JOIN sys.server_principals sp
        ON p.grantee_principal_id = sp.principal_id
    CROSS APPLY
(
    SELECT dss.service_account
    FROM sys.dm_server_services dss
    WHERE dss.servicename NOT LIKE 'SQL Server Agent%'
) ca
WHERE p.class = '105'
      AND ep.type_desc = 'DATABASE_MIRRORING';

DECLARE @SQL VARCHAR(1024),

@SQLSvcAccount VARCHAR(128);

SELECT @SQLSvcAccount = dss.service_account

FROM sys.dm_server_services dss

WHERE dss.servicename NOT LIKE 'SQL Server Agent%';

SELECT ep.endpoint_id,

p.class_desc,

p.permission_name,

ep.name AS EndpointName,

sp.name AS Grantee,

ep.type_desc,

ca.service_account

FROM sys.server_permissions p

INNER JOIN sys.endpoints ep

ON p.major_id = ep.endpoint_id

INNER JOIN sys.database_mirroring_endpoints dme

ON ep.endpoint_id = dme.endpoint_id

INNER JOIN sys.server_principals sp

ON p.grantee_principal_id = sp.principal_id

CROSS APPLY

(

SELECT dss.service_account

FROM sys.dm_server_services dss

WHERE dss.servicename NOT LIKE 'SQL Server Agent%'

) ca

WHERE p.class = '105'

AND ep.type_desc = 'DATABASE_MIRRORING';

In the preceding script, I am looking to see of Grantee and service_account have the same value. If they don’t then I want to grant “connect” to the service_account that is listed. Granting connect is essential to helping us resolve the aforementioned error.

The fix…

We can fix this problem with the following script.

DECLARE @SQL VARCHAR(1024),
        @SQLSvcAccount VARCHAR(128);

SELECT @SQLSvcAccount = dss.service_account
FROM sys.dm_server_services dss
WHERE dss.servicename NOT LIKE 'SQL Server Agent%';

SELECT ep.endpoint_id,
       p.class_desc,
       p.permission_name,
       ep.name AS EndpointName,
       sp.name AS Grantee,
       ep.type_desc,
       ca.service_account
FROM sys.server_permissions p
    INNER JOIN sys.endpoints ep
        ON p.major_id = ep.endpoint_id
    INNER JOIN sys.database_mirroring_endpoints dme
        ON ep.endpoint_id = dme.endpoint_id
    INNER JOIN sys.server_principals sp
        ON p.grantee_principal_id = sp.principal_id
    CROSS APPLY
(
    SELECT dss.service_account
    FROM sys.dm_server_services dss
    WHERE dss.servicename NOT LIKE 'SQL Server Agent%'
) ca
WHERE p.class = '105'
      AND ep.type_desc = 'DATABASE_MIRRORING';

IF NOT EXISTS
(
    SELECT 1/0
    FROM sys.server_permissions p
        INNER JOIN sys.endpoints ep
            ON p.major_id = ep.endpoint_id
        INNER JOIN sys.database_mirroring_endpoints dme
            ON ep.endpoint_id = dme.endpoint_id
        INNER JOIN sys.server_principals sp
            ON p.grantee_principal_id = sp.principal_id
    WHERE p.class = '105' --ENDPOINT class_desc
          AND ep.type_desc = 'DATABASE_MIRRORING'
          AND sp.name = @SQLSvcAccount
          AND p.permission_name = 'CONNECT'
)
BEGIN
    SELECT @SQL = 'GRANT CONNECT ON ENDPOINT::' + ca.endpoint_name + ' TO [' + dss.service_account + ']'
    FROM sys.dm_server_services dss
        CROSS APPLY
    (
        SELECT [name] AS endpoint_name
        FROM sys.database_mirroring_endpoints dme
        WHERE dme.type_desc = 'DATABASE_MIRRORING'
    ) ca
    WHERE dss.servicename NOT LIKE 'SQL Server Agent%';
    PRINT @SQL;
    --EXECUTE (@SQL); --uncomment if you wish for the script to automatically run the fix
END;

SELECT ep.endpoint_id,
       p.class_desc,
       p.permission_name,
       ep.name AS EndpointName,
       sp.name AS Grantee,
       ep.type_desc,
       ca.service_account
FROM sys.server_permissions p
    INNER JOIN sys.endpoints ep
        ON p.major_id = ep.endpoint_id
    INNER JOIN sys.database_mirroring_endpoints dme
        ON ep.endpoint_id = dme.endpoint_id
    INNER JOIN sys.server_principals sp
        ON p.grantee_principal_id = sp.principal_id
    CROSS APPLY
(
    SELECT dss.service_account
    FROM sys.dm_server_services dss
    WHERE dss.servicename NOT LIKE 'SQL Server Agent%'
) ca
WHERE p.class = '105'
      AND ep.type_desc = 'DATABASE_MIRRORING';

DECLARE @SQL VARCHAR(1024),

@SQLSvcAccount VARCHAR(128);

SELECT @SQLSvcAccount = dss.service_account

FROM sys.dm_server_services dss

WHERE dss.servicename NOT LIKE 'SQL Server Agent%';

SELECT ep.endpoint_id,

p.class_desc,

p.permission_name,

ep.name AS EndpointName,

sp.name AS Grantee,

ep.type_desc,

ca.service_account

FROM sys.server_permissions p

INNER JOIN sys.endpoints ep

ON p.major_id = ep.endpoint_id

INNER JOIN sys.database_mirroring_endpoints dme

ON ep.endpoint_id = dme.endpoint_id

INNER JOIN sys.server_principals sp

ON p.grantee_principal_id = sp.principal_id

CROSS APPLY

(

SELECT dss.service_account

FROM sys.dm_server_services dss

WHERE dss.servicename NOT LIKE 'SQL Server Agent%'

) ca

WHERE p.class = '105'

AND ep.type_desc = 'DATABASE_MIRRORING';

IF NOT EXISTS

(

SELECT 1/0

FROM sys.server_permissions p

INNER JOIN sys.endpoints ep

ON p.major_id = ep.endpoint_id

INNER JOIN sys.database_mirroring_endpoints dme

ON ep.endpoint_id = dme.endpoint_id

INNER JOIN sys.server_principals sp

ON p.grantee_principal_id = sp.principal_id

WHERE p.class = '105' --ENDPOINT class_desc

AND ep.type_desc = 'DATABASE_MIRRORING'

AND sp.name = @SQLSvcAccount

AND p.permission_name = 'CONNECT'

)

BEGIN

SELECT @SQL = 'GRANT CONNECT ON ENDPOINT::' + ca.endpoint_name + ' TO [' + dss.service_account + ']'

FROM sys.dm_server_services dss

CROSS APPLY

(

SELECT [name] AS endpoint_name

FROM sys.database_mirroring_endpoints dme

WHERE dme.type_desc =