SQL Server Primary Login RestrictionsApplicationIntent=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?
Does this AnyDice function accurately calculate the number of ogres you make unconcious with three 4th-level castings of Sleep?
Rules about breaking the rules. How do I do it well?
Brexit - No Deal Rejection
Do I need life insurance if I can cover my own funeral costs?
How to simplify this time periods definition interface?
Plot a function of two variables equal 0
My story is written in English, but is set in my home country. What language should I use for the dialogue?
How to answer questions about my characters?
Citation at the bottom for subfigures in beamer frame
What is under these four white covers on the upper part of the Orion capsule?
At what level can a dragon innately cast its spells?
Why would a flight no longer considered airworthy be redirected like this?
No, nay, never, no more
Bastion server: use TCP forwarding VS placing private key on server
Co-worker team leader wants to inject his friend's awful software into our development. What should I say to our common boss?
Making a sword in the stone, in a medieval world without magic
Is it possible that AIC = BIC?
Possible Leak In Concrete
What is IP squat space
Make a transparent 448*448 image
About parabolic Kazhdan Lusztig polynomials
How to deal with a cynical class?
Meaning of "SEVERA INDEOVI VAS" from 3rd Century slab
Is having access to past exams cheating and, if yes, could it be proven just by a good grade?
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?
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
asked 2 hours ago
Dustin LaineDustin Laine
1766
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
asked 2 hours ago
Dustin LaineDustin Laine
1766
No, I checked that. I can connect to the secondary directly with the account
– Dustin Laine
2 hours ago
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
2 hours ago
The user should not be able to connect to the primary replica, only secondary.
– Dustin Laine
2 hours ago
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
asked 2 hours ago
Dustin LaineDustin Laine
1766
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
sql-server availability-groups sql-server-2017
asked 2 hours ago
Dustin LaineDustin Laine
1766
asked 2 hours ago
Dustin LaineDustin Laine
1766
edited 2 hours ago
Dustin Laine
asked 2 hours ago
Dustin LaineDustin Laine
1766
asked 2 hours ago
Dustin LaineDustin Laine
1766
asked 2 hours ago
Dustin LaineDustin Laine
1766
1766
No, I checked that. I can connect to the secondary directly with the account
– Dustin Laine
2 hours ago
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
2 hours ago
The user should not be able to connect to the primary replica, only secondary.
– Dustin Laine
2 hours ago
add a comment |
No, I checked that. I can connect to the secondary directly with the account
– Dustin Laine
2 hours ago
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
2 hours ago
The user should not be able to connect to the primary replica, only secondary.
– Dustin Laine
2 hours ago
No, I checked that. I can connect to the secondary directly with the account
– Dustin Laine
2 hours ago
No, I checked that. I can connect to the secondary directly with the account
– Dustin Laine
2 hours ago
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
2 hours ago
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
2 hours ago
The user should not be able to connect to the primary replica, only secondary.
– Dustin Laine
2 hours ago
The user should not be able to connect to the primary replica, only secondary.
– Dustin Laine
2 hours ago
add a comment |
1 Answer
1
active
oldest
votes
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.
answered 2 hours ago
Sean GallardySean Gallardy
16.7k22654
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
2 hours ago
@DustinLaine Correct (disabled login on the primary)!
– Sean Gallardy
1 hour ago
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
);
);
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered 2 hours ago
Sean GallardySean Gallardy
16.7k22654
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
2 hours ago
@DustinLaine Correct (disabled login on the primary)!
– Sean Gallardy
1 hour ago
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.
answered 2 hours ago
Sean GallardySean Gallardy
16.7k22654
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
2 hours ago
@DustinLaine Correct (disabled login on the primary)!
– Sean Gallardy
1 hour ago
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.
answered 2 hours ago
Sean GallardySean Gallardy
16.7k22654
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.
answered 2 hours ago
Sean GallardySean Gallardy
16.7k22654
answered 2 hours ago
Sean GallardySean Gallardy
16.7k22654
answered 2 hours ago
Sean GallardySean Gallardy
16.7k22654
answered 2 hours ago
Sean GallardySean Gallardy
16.7k22654
16.7k22654
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
2 hours ago
@DustinLaine Correct (disabled login on the primary)!
– Sean Gallardy
1 hour ago
add a comment |
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
2 hours ago
@DustinLaine Correct (disabled login on the primary)!
– Sean Gallardy
1 hour ago
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
2 hours ago
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
2 hours ago
@DustinLaine Correct (disabled login on the primary)!
– Sean Gallardy
1 hour ago
@DustinLaine Correct (disabled login on the primary)!
– Sean Gallardy
1 hour ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
No, I checked that. I can connect to the secondary directly with the account
– Dustin Laine
2 hours ago
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
2 hours ago
The user should not be able to connect to the primary replica, only secondary.
– Dustin Laine
2 hours ago