0

So we just switched to SQL Managed Instnace. As much as it pains me, we had more than a few places where untrained users were querying our production database(s). The switch to a business critical SQL Managed Instance was made partially because we could have them connecting to a read-only-replica of the DB.

Upon digging more, it seems that to connect to the read-only-replica (ror), they're going to need to open SSMS, hit "Advanced Options" then go over to extra parameters and put ApplicationIntent=ReadOnly. This is a bit of a bummer because 1. That means that many of them will probably mistakenly connecto to the "real" db and potentially cause havoc and 2. Thats a lot of extra steps for a user.

My Questions:

  1. Is there a way to use SSMS to bake a connection into their system somehow that automatically sets the paramaters?
  2. If not, is there a way to deny them connection if they DONT Have those parameters?
  3. Side Note: I put a CNAME in my private DNS to cname sqlprod01.mydomain.com to the endpoint I get "bad login" but when i keep that same login info and hit the endpoint directly, it works fine. What's up with that?
Daniel Williams
  • 167
  • 1
  • 10
  • A solution for untrained users is to train them. Write a **How-To** document with pretty pictures and details. Include a section on how to solve problems. Then only give them the replica endpoint. – John Hanley Aug 12 '21 at 03:04
  • "Only Give them the replica endpoint" -- That's my problem. The replica and non-replica endpoints are the same. You access one by adding the arguements and you access the other by ... not doing that. I would love if there was a way to deny them access to the non-replica endpoint in Sql Managed Instance – Daniel Williams Aug 12 '21 at 07:28

0 Answers0