Ggthjy

Does an advisor owe his/her student anything? Will an advisor keep a PhD student only out of pity?

Multiplicative persistence

Infinite dials to reset ever?

Are Captain Marvel's powers affected by Thanos' actions in Infinity War

Count the occurrence of each unique word in the file

What changes for testers when they are testing in agile environments?

Not using 's' for he/she/it

Why did the EU agree to delay the Brexit deadline?

Pre-modern battle - command it, or fight in it?

Did arcade monitors have same pixel aspect ratio as TV sets?

How to implement a feedback to keep the DC gain at zero for this conceptual passive filter?

What does chmod -u do?

Is it improper etiquette to ask your opponent what his/her rating is before the game?

Terse Method to Swap Lowest for Highest?

Fear of getting stuck on one programming language / technology that is not used in my country

Are paving bricks differently sized for sand bedding vs mortar bedding?

Travelling outside the UK without a passport

What if a revenant (monster) gains fire resistance?

Lowest total scrabble score

How can Trident be so inexpensive? Will it orbit Triton or just do a (slow) flyby?

Biological Blimps: Propulsion

New brakes for 90s road bike

How does the math work for Perception checks?

The screen of my macbook suddenly broken down how can I do to recover

SQL Server Primary Login Restrictions

ApplicationIntent=ReadOnly Traffic when no Readable Secondary AvailableAvailability Group: ReadIntent connection failingAlways On Availability Group, Always redirect user to read only instanceAlwaysON - Force users connection to use Readable Secondary NodesConnection to secondary DB in AlwaysOn High AvailabilityLoad balancing reads SQL Server 2016 AGAlways On Availability Group ApplicationIntent=ReadOnly Not routing to SecondaryQuestions on Availability Group Readable Secondaryhow do I ensure that users in AlwaysOn Availability Group only connect to the secondary, even in a failoverHow to let particular logins to work on the secondary replica only?

4

I have read only routing setup and working fine. I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

I have seen a lot of topics on this and they all seem to suggest disabling the login on the primary. When I do this connections to the listener with ApplicationIntent=ReadOnly fail with Login failed for user ''. Reason: The account is disabled.

I have ensured the accounts are the same SID.

Thanks for any help.

sql-server availability-groups sql-server-2017

share|improve this question

edited Mar 14 at 18:24

Dustin Laine

asked Mar 14 at 18:18

Dustin LaineDustin Laine

1787

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  No, I checked that. I can connect to the secondary directly with the account
  
  – Dustin Laine
  Mar 14 at 18:25
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you please be more specific on this point, "However I want to prevent the user from accessing the primary." as it will change my answer.
  
  – Sean Gallardy
  Mar 14 at 18:29
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The user should not be able to connect to the primary replica, only secondary.
  
  – Dustin Laine
  Mar 14 at 18:34
  

  
  
  
  

add a comment | 

4

I have read only routing setup and working fine. I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

I have seen a lot of topics on this and they all seem to suggest disabling the login on the primary. When I do this connections to the listener with ApplicationIntent=ReadOnly fail with Login failed for user ''. Reason: The account is disabled.

I have ensured the accounts are the same SID.

Thanks for any help.

sql-server availability-groups sql-server-2017

share|improve this question

edited Mar 14 at 18:24

Dustin Laine

asked Mar 14 at 18:18

Dustin LaineDustin Laine

1787

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  No, I checked that. I can connect to the secondary directly with the account
  
  – Dustin Laine
  Mar 14 at 18:25
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you please be more specific on this point, "However I want to prevent the user from accessing the primary." as it will change my answer.
  
  – Sean Gallardy
  Mar 14 at 18:29
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The user should not be able to connect to the primary replica, only secondary.
  
  – Dustin Laine
  Mar 14 at 18:34
  

  
  
  
  

add a comment | 

I have read only routing setup and working fine. I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

I have seen a lot of topics on this and they all seem to suggest disabling the login on the primary. When I do this connections to the listener with ApplicationIntent=ReadOnly fail with Login failed for user ''. Reason: The account is disabled.

I have ensured the accounts are the same SID.

Thanks for any help.

sql-server availability-groups sql-server-2017

share|improve this question

edited Mar 14 at 18:24

Dustin Laine

asked Mar 14 at 18:18

Dustin LaineDustin Laine

1787

share|improve this question

edited Mar 14 at 18:24

Dustin Laine

asked Mar 14 at 18:18

Dustin LaineDustin Laine

1787

share|improve this question

edited Mar 14 at 18:24

Dustin Laine

asked Mar 14 at 18:18

Dustin LaineDustin Laine

1787

asked Mar 14 at 18:18

Dustin LaineDustin Laine

1787

asked Mar 14 at 18:18

Dustin LaineDustin Laine

1787

1 Answer
1

active

oldest

votes

5

I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

More specifically:

The user should not be able to connect to the primary replica, only secondary.

In this case, it's not possible to do what you're wanting. You can't use read only routing with this as the first step in read only routing is to connect to the primary to check if the requirements to meet read only routing are correctly used and then get the metadata from the primary to understand where the new connection should take place.

You can, however, use something such as a network load balance appliance to dynamically update a cname or A record (AAAA if IPv6) to always point to a secondary. This would be specific to the load balancing software/hardware you choose to use. You could also write your own with a trivial amount of work.

share|improve this answer

answered Mar 14 at 18:39

Sean GallardySean Gallardy

16.9k22654

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think you cleared this up, but please let me confirm. Reading the information I previously found there is obscurity in if a listener is used. In my scenario I can still connect to the secondary directly using the disabled login approach, but they would not benefit from the routing features of the AG. If I want to use the listener then the login needs to exist and be enabled on both. Does this sound right?
  
  – Dustin Laine
  Mar 14 at 19:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @DustinLaine Correct (disabled login on the primary)!
  
  – Sean Gallardy
  Mar 14 at 19:54
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232174%2fsql-server-primary-login-restrictions%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

5

I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

More specifically:

The user should not be able to connect to the primary replica, only secondary.

In this case, it's not possible to do what you're wanting. You can't use read only routing with this as the first step in read only routing is to connect to the primary to check if the requirements to meet read only routing are correctly used and then get the metadata from the primary to understand where the new connection should take place.

You can, however, use something such as a network load balance appliance to dynamically update a cname or A record (AAAA if IPv6) to always point to a secondary. This would be specific to the load balancing software/hardware you choose to use. You could also write your own with a trivial amount of work.

share|improve this answer

answered Mar 14 at 18:39

Sean GallardySean Gallardy

16.9k22654

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think you cleared this up, but please let me confirm. Reading the information I previously found there is obscurity in if a listener is used. In my scenario I can still connect to the secondary directly using the disabled login approach, but they would not benefit from the routing features of the AG. If I want to use the listener then the login needs to exist and be enabled on both. Does this sound right?
  
  – Dustin Laine
  Mar 14 at 19:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @DustinLaine Correct (disabled login on the primary)!
  
  – Sean Gallardy
  Mar 14 at 19:54
  

  
  
  
  

add a comment | 

5

I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

More specifically:

The user should not be able to connect to the primary replica, only secondary.

In this case, it's not possible to do what you're wanting. You can't use read only routing with this as the first step in read only routing is to connect to the primary to check if the requirements to meet read only routing are correctly used and then get the metadata from the primary to understand where the new connection should take place.

You can, however, use something such as a network load balance appliance to dynamically update a cname or A record (AAAA if IPv6) to always point to a secondary. This would be specific to the load balancing software/hardware you choose to use. You could also write your own with a trivial amount of work.

share|improve this answer

answered Mar 14 at 18:39

Sean GallardySean Gallardy

16.9k22654

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think you cleared this up, but please let me confirm. Reading the information I previously found there is obscurity in if a listener is used. In my scenario I can still connect to the secondary directly using the disabled login approach, but they would not benefit from the routing features of the AG. If I want to use the listener then the login needs to exist and be enabled on both. Does this sound right?
  
  – Dustin Laine
  Mar 14 at 19:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @DustinLaine Correct (disabled login on the primary)!
  
  – Sean Gallardy
  Mar 14 at 19:54
  

  
  
  
  

add a comment | 

I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

More specifically:

The user should not be able to connect to the primary replica, only secondary.

In this case, it's not possible to do what you're wanting. You can't use read only routing with this as the first step in read only routing is to connect to the primary to check if the requirements to meet read only routing are correctly used and then get the metadata from the primary to understand where the new connection should take place.

You can, however, use something such as a network load balance appliance to dynamically update a cname or A record (AAAA if IPv6) to always point to a secondary. This would be specific to the load balancing software/hardware you choose to use. You could also write your own with a trivial amount of work.

share|improve this answer

answered Mar 14 at 18:39

Sean GallardySean Gallardy

16.9k22654

share|improve this answer

answered Mar 14 at 18:39

Sean GallardySean Gallardy

16.9k22654

answered Mar 14 at 18:39

Sean GallardySean Gallardy

16.9k22654

answered Mar 14 at 18:39

Sean GallardySean Gallardy

16.9k22654