The blog post below may be very simple to some admins who are familiar with SQL Server 2012, but recently I ran across an article about configuring SQL database replicas for Management Points in ConfigMgr 2012. In that article, there was a step asking me to add a computer account to the db_datareader role in SQL. It did not go into depth on how to do this, so I put the following post together to outline that process.
While the post below will discuss adding user accounts to SQL, my main concern in writing this is to document how to get a computer account added. It requires a few extra steps, but can definitely be done.
I hope it’s helpful.
When working with Configuration Manager, it is sometimes necessary to make changes to the security of the underlying SQL database. A specific example of this is when setting up a database replica on a Management Point. One of the steps in this process is outlined below:
This statement can be found in the following document:
Configure Database Replicas for Management Points
When encountering instructions such as this, it is helpful to understand the underlying SQL processes necessary so that security can be correctly set up.
In SQL Server 2012, there are three essential definitions that are important to understand before attempting the task listed above. These are:
When using Windows authentication, the underlying OS is responsible for ensuring that authentication happens. The following object types can be used when configuring SQL Logins using Windows authentication:
Best practices when configuring access to resources dictates that a security group is configured and granted the necessary permissions and rights to the resource. Then users, computers and even other groups can be added to the first group as needed. In other cases, it may be necessary to add a user or computer object directly.
The principle of assigning a Windows account (group, user or computer) to a role involves the following steps:
For the present document, we will add a computer object directly. Though the process is largely the same, the steps of doing this are slightly more complicated than adding a user or group. Differences between these two processes will be obvious, but will be mentioned for clarity at the appropriate points.
Per the statement in the summary above, our interest is in adding the Management Point computer to the db_datareaders database role. The following steps outline in general how to accomplish this task. I have deliberately included steps that will work with users and groups, but not with computer objects for completeness.
Creating a SQL Login (users and groups)
The following steps work when the goal is to add a user or group object. It does not allow adding a computer object, as noted at the appropriate point below.
Before a Windows user can be added to the desired role, it must first have a SQL Login created. To do this, take the following steps:
NOTE: If the goal is to use this process to select a computer account, you will receive the following message after typing in the computer name and selecting ‘Check Names’:
When selecting ‘Object Type’s to ensure that Computers are included in the results, it becomes obvious that this method cannot be used to associate a computer with a SQL Login.
This process works well when adding a user or group. But if there is a need to add a computer, that must be accomplished a different way
Creating a SQL Login (computers)
The principle remains the same if we need to add a computer object to a database role in SQL. We must still create a SQL Login, associate that login with a database user, and place that database user into the desired database role. However, creating a SQL Login for a computer must be handled through a SQL statement directly.
The following SQL statement will allow adding a computer object as a SQL Login:
create login [test\na-pr1-ss$] from windows;
In my case, I am adding the computer object known as na-pr1-ss that belongs in the Active Directory forest known as ‘test’. Note three things about this SQL statement:
Once the SQL statement has been run, if it’s successful you can verify it was successful with the following SQL query:
select * from sys.server_principals where name like '%na%'
It is also possible to expand Security\Logins as shown in the screenshot above. This also shows that the computer account has been added.
Associating the SQL Login with a Database User
From this point forward, the process is the same regardless of whether you are working with a user, group or computer. Everything is now a SQL Login and SQL Logins can be handled in a uniform manner.
To create a new Database User that is associated with your newly created SQL Login, take the following steps:
NOTE: This is not the same Security container worked with in the steps above. This container is specific to the SQL Database itself. SQL Logins are created at the top-level Security container. Users are created within the database-specific Security container.
Add the Database User to a Database Role
At this point, we have a SQL Login that has been associated with a database user. Finally, we can take the final step and add the database user to the desired role. In our case, we are interested in the db_datareader role.
To see a list of the fixed roles within the SQL database, navigate to Databases\<SQL Database>\Security\Roles\Database Roles.
To add the database user to the desired database role (in this case db_datareader), take the following steps:
The process of adding a objects to a database role in SQL is not very well documented. The majority of the documentation available assumes either a user or group will be used, and only a few references in social sites even raised the question of adding a computer account. This document’s purpose has been to clear up that mystery.
Very, very helpful piece of information. Thanks Scott!
Very useful article....!!!
Thank you ver helpful. A problem we still have is at the sql login level. If we add ther the domain admins group and not the single users from this group we have the effect, that the admin can login but cannot see all content like the logins (only sql
login like sa but no tohter domain user or groups)
Scott maybe you also a solution for this case, would be very happy about.!
And I got the solution for one more problem....Thank you :-)