SQL Server Network Security



[pic]

SQL Server Network Security (Overview)

We follow Microsoft’s recommendations that Microsoft SQL Server security be implemented through Windows Authentication methods.

If you are going to run eVo on a single computer under a user account that has Administrator privileges, you probably do not have to do anything else. However, if you will be running eVo over a network, the following instructions may be helpful.

Logins and Windows Authentication (Overview)

The only way anyone can connect to Microsoft SQL Server is via a login. This does not mean that every user has to have a specific login within Microsoft SQL Server. With Windows Authentication, if a user account belongs to a specific Windows group, and if that group is contained within Microsoft SQL Server, the user account will have access to Microsoft SQL Server.

When a database is created initially only the database owner has any rights to the database. It is common practice to create a Windows group and place Windows user accounts into that group. The rest of this document summarizes the steps to do this.

NOTE: If you are running Microsoft Windows Vista or Windows 7, please read the section titled “Note to Microsoft Windows Vista and Windows 7 Users” near the end of this document before proceeding.

Assuming you are an Administrator on the server, you already belong to an Administrator group. This Administrator group is automatically included in Microsoft SQL server. In this case, your user account is already able to connect to Microsoft SQL Server.

By extension, if there are other user accounts on the server defined as Administrators, they too should be able to connect to Microsoft SQL Server since they belong to the Administrators group.

Create a Windows Group

1. Open the Windows Control Panel

2. Select Administrative Tools

3. Select Computer Management

4. From the Computer Management console, select Local Users and Groups(Groups. In the figure below, you can see that there are already several groups defined, including some groups for Microsoft SQL Server.

[pic]

5. Right click on Groups and select New Group

NOTE: We are going to add a group named “SQLUsers”. You can call the group anything you like, but you may want to follow the examples here for consistency.

6. From the New Group dialog, we will enter “SQLUsers” as the Group name. You may enter whatever you like for the Description and click Create and then close the dialog.

[pic]

7. The Computer Management console should now show the SQLUsers group under Local Users and Groups(Groups.

[pic]

You are now ready to add users to this newly created Windows group.

Add User To Windows Group

1. From the Computer Management console, under Local Users and Groups(Groups, double click on the SQLUsers Windows group.

[pic]

2. From the Group Properties dialog, click the Add button to add Users to the group.

NOTE: It is presumed that you already know how to add users to Windows, and that you have already added the users you need.

[pic]

3. From the Select Users dialog, you can just type the user name into the box as shown. Then click Check Names.

In most cases, it will come back with the server name and a backslash in front of the user name.

NOTE: You must type the user name exactly as it was created under Windows, although capitalization does not matter.

[pic]

4. Click OK on the Select Users dialog to add the user to the Windows group.

Adding The Windows Group To SQL Server

1. Microsoft SQL Server must be configured so that our Windows group is considered as a trusted group. Open SQL Server Management Studio by selecting Start(All Programs(Microsoft SQL Server(SQL Server Management Studio.

NOTE: The start menu options may vary from one Windows operating system to another and from one version of Microsoft SQL Server to another.

2. From the SQL Server Management Studio, navigate to the Security(Logins node as shown in the figure below.

[pic]

3. Right click on the Logins node and select New Login.

4. From the New Login dialog, click Search to begin searching for the desired Windows group.

[pic]

5. The Select User or Group dialog will be displayed.

Microsoft SQL Server will not search for Windows groups by default, so we will have to configure it to allow searching under Windows groups. To configure this, click Object Types and then make sure that the Groups box is checked.

[pic]

[pic]

6. From the Select User or Group dialog, click Advanced and the dialog will expand as shown in the figure below.

[pic]

7. Click Find Now to perform a search. A list of Windows users and groups will be displayed.

Scroll down to the SQLUsers group and select it, then click OK.

[pic]

8. The Select User or Group dialog now shows the SQLUsers group. Click OK to close this dialog.

[pic]

9. We need to give this group access to the eVo database.

IMPORTANT: It is vital that you only allow users or groups of users access to the resources they need, so that security is limited to as “as needed” basis.

Click the User Mapping option on the left to display the databases and roles. Check the Map check box in the row with the database name of “eVo”. Then click db_datareader, db_datawriter, db_ddladmin, and public in the database role membership list.

[pic]

10. Click on Status. Make sure that the Permission to connect and the Login options are granted and enabled, respectively.

[pic]

Summary

You could create a login in Microsoft SQL Server for each separate user using approximately the same procedure as above, but that is a lot of work and does not really gain anything meaningful.

Using the concept of users and groups as described in this document is much easier to maintain. You now only have to add or remove users from the SQLUsers group to allow or deny access to Microsoft SQL Server.

Note to Microsoft Windows Vista and Windows 7 Users

By default, Microsoft Windows Vista does not automatically give Administrative rights to Microsoft SQL Server users, even if that user is an Administrator in Windows. You can overcome this limitation by clicking Start(All Programs(Microsoft SQL Server 2005.

Then find SQL Server Management Studio and Right-Click, then select “Run as Administrator”. From there, you can create logins as explained above.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download