You’re a consultant or remote worker, and you’re connecting into your work or client network using a VPN. But when you try to connect to the SQL Server, you get this.
There are a few simple workarounds, each with their own pros and cons.
AlwaysOn Availability Groups are a reasonably simple way to set up disaster recovery (DR) for your SQL Server environment, and with fairly little effort, you can get a bit of high availability (HA) from it as well. But there are a few gotchas, the most obvious of them being that Availability Groups only synchronize specific user-databases, not the entire server setup.
Things that are not included in AGs include logins, SQL Server Agent jobs, SSIS packages stored in SQL Server, linked servers and server settings. You could sync these manually (as is often the case), but wouldn’t you just love to have an automated process do all this for you?
In this post, we’ll look at logins. For the sake of simplicity, I’ll assume that you have a primary replica with a single AG and any number of secondary replicas. The logic holds true if you have multiple AGs, it just gets trickier.
There are a number of layers in the SQL Server security model, giving you a nearly infinite number of ways to set up access control on your server and databases. Security is a huge topic, and there are literally entire books on it, so this series of articles is designed to give you just a quick overview of the SQL Server security model to get you started.
In this first installment, I’ll go through the different types of security principals that are available, as well as how they connect to each other.
Don’t you just hate it when you once again have to look to Google for help on fixing some obscure Kerberos related authentication problem on SQL Server?
Luckily, I found a blog post about fixing Kerberos problems using a new tool from Microsoft, the Kerberos Configuration Manager for SQL Server. This tool will go through your settings and SPNs and what-not, to help you resolve the problem.
Application roles provide a practical way to assign application-specific permissions in your database and to make sure that your applications always use a defined login. Not to be confused with actual roles, application roles are more like users in the database.