A Security Overview of Microsoft Visual Studio 2005 Team Edition for Database Professionals

  • Article

 

Richard Waymire
Microsoft Corporation

January 2007

Applies to:
   Microsoft Visual Studio 2005 Team Edition for Database Professionals

Summary: Microsoft Visual Studio 2005 Team Edition for Database Professionals (DB Pro) is the most recent addition to the Microsoft Visual Studio Team Suite of products. The mission of this product is to bring developers of database application code (Transact-SQL for Microsoft SQL Server 2000 and Microsoft SQL Server 2005) into the application development life cycle. For more product information about Visual Studio 2005 Team Edition for Database Professionals, see the Database Professional Team Center for Visual Studio Team System.

To integrate database development into the overall life cycle most effectively, you must understand the variety of security implications in Team Edition for Database Professionals. These implications include how to set up and configure the product more securely, how to use the security-related features of the product, and best practices for a more secure implementation of your database projects. (11 printed pages)

Contents

Permissions for Installation and Configuration
Permissions for Schema Development and Deployment
Security Objects in Database Projects
Security Considerations for Features with External Access
Conclusion

Permissions for Installation and Configuration

When you install Team Edition for Database Professionals, you should configure some components to make your installation more secure.

Installing and Running Team Edition for Database Professionals

To install Team Edition for Database Professionals, you must log on with an account that has administrative permissions on the local computer. During the installation, you will create files and registry keys, and you will register assemblies in the global assembly cache.

To run Team Edition for Database Professionals, however, you need only standard user permissions for Windows and permissions for SQL Server 2005, as mentioned in the following section. By default, all user settings are kept in either the HKEY_CURRENT_USER portion of the registry or in your drive:\\My Documents directory.

When this document was written, Windows Vista was not supported with Team Edition for Database Professionals.

Installing SQL Server 2005

If SQL Server 2005 is not already installed on the same computer as Team Edition for Database Professionals, you must log on with an account that has administrative permissions on that computer and install one of the following:

  • SQL Server 2005 Developer Edition
  • SQL Server 2005 Enterprise Edition
  • SQL Server 2005 Enterprise Evaluation Edition

If you have Visual Studio 2005 Professional Edition or Visual Studio Team Edition, you can install SQL Server 2005 Developer Edition from the installation media for that product.

This instance of SQL Server, known as the DesignDB instance, acts as a validation engine to support database projects. Although you might never need to use this instance of SQL Server directly, the SQL Server database engine (MSSQLServer) service must be running for you to create and use database projects.

The service account of the DesignDB instance must be either Local System account or running as the target user of Visual Studio.

For information about how to make your SQL Server 2005 more secure (including best practices), see "SQL Server 2005: Security and Protection" in the SQL Server 2005 Technical Documentation Library.

Installing and Enabling Other Components of SQL Server

If you intend to create or use projects that reference the full-text indexing services of SQL Server, you must install the full-text search service along with your installation of SQL Server 2005. This service is installed by default for SQL Server 2005 Developer Edition and SQL Server 2005 Enterprise Edition.

If you intend to use SQL common language runtime (SQLCLR or .NET assemblies) in your database projects, you must enable it because it is disabled by default. To enable SQLCLR, you must log on to Windows with an account that has sysadmin permissions on your DesignDB instance of SQL Server. After you install SQL Server, you can configure SQLCLR through the SQL Server 2005 Surface Area Configuration utility or by running Transact-SQL code.

To open the Surface Area Configuration utility, click Start, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration. To specify the instance of SQL Server that you intend to use for your DesignDB installation, click the instance in the tree. (SQLEXPRESS is the instance name in Figure 1.) To enable SQLCLR for the instance that you specified, click CLR Integration in the tree, select the Enable CLR integration check box, and click OK. The change occurs immediately for the instance of SQL Server that you specified.

Click here for larger image

Figure 1. Enabling CLR integration in the SQL Server 2005 Surface Area Configuration utility (Click picture for a larger image)

As an alternative, you can run the following Transact-SQL code while you are connected to the DesignDB instance as a member of the SQL Server sysadmin server role.

Exec sp_configure 'clr enabled', 1
Go
Reconfigure with override
Go

After you complete this step, your team can create CLR objects as part of your database projects for SQL Server 2005.

Creating a Windows Security Group for SQL Server

Many people run with sysadmin permissions against their local, private copies of SQL Server. However, you do not usually need that much access to use the DesignDB instance. As a best practice, you should consider creating a Windows security group on the local computer, adding all users who will use that DesignDB instance as members of that security group, and then granting that group access to SQL Server.

In the Command Prompt window, type the following:

Net localgroup 'VSTEDPUsers' /ADD
Net localgroup 'VSTEDPUsers' <YourUserName> /ADD

Run the second "net localgroup" command once for each user whom you want to add to the Windows security group.

Or, from the graphical interface (on Microsoft Windows XP):

  1. Click Start, and then click Control Panel.
  2. Double-click Administrative Tools, and then double-click Computer Management.
  3. In the console tree, expand System Tools, expand Local Users and Groups, and then click Groups.
  4. On the Action menu, click New Group.
  5. In Group Name, type a name for the new group (VSTEDPUsers, in our example).
  6. In Description, type a description of the new group.
  7. To add one or more users to the new group, click Add.
  8. Click Create, and then click Close.

After you have created the security group, connect to SQL Server again by using an instance of Visual Studio's query editor, SQL Server Management Studio, or a command-line utility such as sqlcmd. Run the following Transact-SQL to add the Windows security group as a valid SQL Server login, and make the login a member of the dbcreator and securityadmin server roles in SQL Server.

CREATE LOGIN [ComputerName\VSTEDPUsers] FROM WINDOWS

EXEC sp_addsrvrolemember 'ComputerName\VSTEDPUsers','dbcreator'
EXEC sp_addsrvrolemember 'ComputerName\VSTEDPUsers','securityadmin'
GRANT VIEW SERVER STATE TO [ComputerName\VSTEDPUsers]

Team Edition for Database Professionals requires this minimum set of permissions for the DesignDB instance of SQL Server. For more information about this server role, see "Server-Level Roles" in SQL Server 2005 Books Online.

Permissions for Schema Development and Deployment

Understanding Organizational Environments

The application development life cycle of database schemas involves test deployments, just as other Windows applications do. The end goal of test deployment, of course, is to deploy schema changes to a production server. However, in most environments, database developers do not have access to production environments. This situation presents a challenge for users of Team Edition for Database Professionals. The typical database developer does not have permission to log in to production systems, let alone read schema information from them.

Therefore, a typical use scenario for Team Edition for Database Professionals involves at least two users to build and deploy database schemas. A database administrator or other professional with access permissions to a production database creates a database project and then imports the existing database schema from that production environment. This action downloads all schema information for that database into files in the database project.

After the database administrator saves the project and checks it in to a source control system, a database developer (who does not necessarily have any rights to the original production system) can work with, modify, and build test deployments of the schema. The test deployments would occur first on the developer's "sandbox," a personal target database that the database developer can fully control. Additionally, the developer can delete and replace the information and schemas in the sandbox at will to facilitate testing. The developer can change the schema, build a data generation plan or plans for that schema, and create unit tests for stored procedures, functions, and other objects. The developer then deploys the schema to the sandbox, generates data for the tables in that schema, and runs unit tests.

When the developer finalizes schema changes and verifies that the test results are complete and correct, the project can be deployed to an integration testing environment. After the changes are tested with other developers' work and the changes are ready to be moved to production, the database administrator (or another user who has the appropriate permissions) can open the project, do a final build, and deploy the changes.

Setting Permissions by Environment

Required permissions for each of these environments will vary depending on exactly which actions you want to take. However, you can follow some general guidelines for each environment.

Sandbox

Used by a single developer, a sandbox is typically either a SQL Server 2000 or SQL Server 2005 environment that is installed either locally or remotely. The sandbox is the target for repeated deployments of the project, which almost certainly require the developer to create databases. Therefore, the developer must have either the CREATE DATABASE permission or membership in the dbcreator server role. If the target database is SQL Server 2005 and you plan to use CLR assemblies for which the EXTERNAL_ACCESS or UNSAFE option is set, the developer must be a member of the sysadmin role on the sandbox.

Integration Testing

The developer needs similar permissions to work in the integration test environment, as in the sandbox. However, those developers who only deploy changes to an existing database need just the permissions to update the schema on the target database and to insert, update, and delete tables that data generation plans will fill with data.

Production

Only operations staff and database administrators should have permission to update a database schema in a production environment. The exact nature of the changes determines exactly which permissions the team member needs. For example, the team member needs CREATE DATABASE permissions to deploy a new database. Otherwise, the team member needs permissions on each object that will change, and additional permissions are required if the pre-deployment and post-deployment scripts change server settings. For a successful deployment, team members must specifically coordinate change deployment to coincide with changes in the applications that access the database. In addition, all affected databases should be backed up before any changes are deployed.

Security Objects in Database Projects

Security objects in database projects vary depending on the version of SQL Server that the project supports. Some objects are handled through pre-deployment and post-deployment scripts, but most objects are included as core project objects.

Database Projects for SQL Server 2000

Security-related objects in SQL Server 2000 are handled either as full members of a database project or through pre-deployment and post-deployment scripts.

Security objects in database projects

A database project directly supports users, application roles, and database roles.

Adding users

For a database project that is targeted at SQL Server 2000, sp_grantdbaccess is the default stored procedure for adding a user to the project.

You must specify a SQL Server login name for the user, and you can specify a name for use in the database. If you do not specify a name for use in the database, the SQL Server login name is used by default.

  sp_grantdbaccess [@loginame =] 'login'    [,[@name_in_db =] 'name_in_db' 
[OUTPUT]]

When you add a user to a database project (as with all other entries in a database project), the user is created in the DesignDB instance that supports the database project. However, you cannot add a user to a database in SQL Server 2000 without a supporting SQL Server login. To bypass this limitation, the project system dynamically generates a SQL Server login with the name that is scripted in the sp_grantdbaccess stored procedure call with a call to sp_addlogin. If the SQL Server login already exists in the DesignDB instance, it is not an error condition because the existing SQL Server login can be reused, and no SQL Server login creation is required. However, the SQL Server login is not automatically scripted to the Logins.sql pre-deployment script, as occurs when you import a script or a schema.

You can use sp_adduser to add users to your database project, if you want. However, in SQL Server 2000, this stored procedure is intended for backward-compatible use only and is not recommended.

Adding application roles

You can use the sp_addapprole system stored procedure to add application roles. If you use this stored procedure, you must specify the role name, and you can specify a password.

  Sp_addapprole [ @rolename = ] 'role'     , [ @password = ] 'password'

By specifying a password, you reduce the risk that underlies elevating the application's access to schema objects. However, the password is then stored in plain text as part of your database project. If you specify a password, you should consider encrypting the project files to protect the password from unauthorized access.

Adding database roles

You can use the sp_addrole stored procedure to add a database role. If you use this stored procedure, you must specify the name of the role, and you can specify the owner of the role. (The owner must be a valid user in the database.)

  sp_addrole [ @rolename = ] 'role'     [ , [ @ownername = ] 'owner' ]

However, database role membership is not tracked as part of the role creation script. You must script the mapping of users into a database role in a post-deployment script. You should use the RoleMemberships.sql script to add calls to sp_addrolemember. If you import an existing database schema, this post-deployment file will contain role membership calls in the form of calls to sp_addrolemember.

Pre-deployment and post-deployment scripts

Other objects are handled through scripting in pre-deployment and post-deployment scripts, instead of directly in database projects. Objects in these files are not created in the DesignDB instance (with the SQL Server login exception noted previously), and you must directly edit these scripts to change them. Database projects provide no additional support for these files. However, Team Edition for Database Professionals will check the files for syntax before building and deploying them.

Adding logins

The Logins.sql file will be added as a pre-deployment script file. This file will contain calls to sp_addlogin for SQL Server logins that support users who are added to the database. If you import an existing database schema, SQL Server logins from the source server will not be imported into your project. Only SQL Server logins that are explicitly required to support database users in the project will be added to this script file automatically. If additional SQL Server logins should be added to a server upon deployment, you should add them to this script file.

Adding linked servers

Linked servers will not be scripted by the import functionality of a database project. If you import a script that has scripts to create linked servers (sp_addlinkedserver), these calls are routed to the LinkedServers.sql file.

Importing and creating permissions

Permissions are routed to the Permissions.sql file when you import an existing schema or a script. These permissions include all grant, revoke, and deny permissions that exist either in your script or in the source database when you import a schema.

Database Projects for SQL Server 2005

Database projects for SQL Server 2005 have more security objects than database projects for SQL Server 2000. In addition, the syntax for creating the some of the same objects has changed significantly.

Security objects in database projects

Database projects for SQL Server 2005 directly support schemas, users, application roles, and database roles.

Creating schemas

Schemas existed in SQL Server 2000, but they were just unnamed wrappers around object creation scripts. In SQL Server 2005, schemas are the namespaces for objects in a database. Database projects support schema creation with the CREATE SCHEMA statement.

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]
<schema_name_clause> ::=
    {
        schema_name
    | AUTHORIZATION owner_name
    | schema_name AUTHORIZATION owner_name
    }
<schema_element> ::=
    {
        table_definition | view_definition | grant_statement
        revoke_statement | deny_statement
    }

The schema_element clause is not supported if you are adding a schema in a database project. This clause is for backward compatibility with SQL Server 2000 schemas.

The schema name will be the namespace for other objects, such as the default schema "dbo" that is used in many databases.

Schemas are added to the DesignDB instance when you add them to a database project (assuming that they are syntactically correct).

Adding users

In SQL Server 2005, you use the CREATE USER statement, instead of a stored procedure, to add users to the database. You can use the sp_grantdbaccess stored procedure, but it is not recommended.

CREATE USER user_name  [ { { FOR | FROM }
      {
        LOGIN login_name
        | CERTIFICATE cert_name
        | ASYMMETRIC KEY asym_key_name
      }
      | WITHOUT LOGIN    ]
    [ WITH DEFAULT_SCHEMA = schema_name ]

Because users are part of the namespace in SQL Server 2000, it is critical for them to be full members of a database project. However, SQL Server 2005 does not necessarily have the same requirement. Schemas, not users, are the namespace reference. A user might have the same name as a schema, but they are still two database objects. In fact, if you use sp_grantdbaccess in SQL Server 2005, a user and a schema with the same name are created automatically.

By default, users in database projects for SQL Server 2005 are created with the "WITHOUT LOGIN" clause, so that matching SQL Server logins are not auto-generated. However, if you provide a SQL Server login reference, the same logic is used as in a database project for SQL Server 2000 to create a supporting SQL Server login in the DesignDB instance. However, the SQL Server login is not automatically scripted to the Logins.sql pre-deployment script, as occurs when you import a script or a schema.

If a user is created with a reference to a certificate or an asymmetric key, Team Edition for Database Professionals automatically creates a certificate or asymmetric key in the DesignDB instance to support the user, with a randomly generated password. However, a certificate or asymmetric key script will not be automatically generated or added to the EncryptionKeysandCertificates.sql pre-deployment script. You must manually add CREATE CERTIFICATE or CREATE ASYMMETRIC KEY statements to these files to support deployment of these database users.

Adding application roles

You can add an application role by using either the CREATE APPLICATION ROLE statement or the sp_addapprole stored procedure, which is backward compatible. As previously mentioned, you must specify the name of the application role, and you must also specify a password in SQL Server 2005.

CREATE APPLICATION ROLE application_role_name
    WITH PASSWORD = 'password' [ , DEFAULT_SCHEMA = schema_name ]

Again, you should consider carefully whether you want the production password to an application role to be stored offline in a file. If you must store that information, you should consider encrypting that file to protect it from unauthorized access.

Adding database roles

You can add a database role by using either the CREATE ROLE statement or the sp_addrole stored procedure, which is backward-compatible.

CREATE ROLE role_name [ AUTHORIZATION owner_name ]

However, database role membership is not tracked as part of the role-creation script. You must script the mapping of users into a database role in a post-deployment script. You should use the RoleMemberships.sql script to add calls to sp_addrolemember. If you import an existing database schema, this post-deployment file will contain role membership calls in the form of calls to sp_addrolemember.

Pre-deployment and post-deployment scripts

Other objects are handled through scripting in pre-deployment and post-deployment scripts, instead of directly in database projects. Objects in these files are not created in the DesignDB instance (with the SQL Server login exception noted previously), and you must directly edit these scripts to change them. Database projects provide no additional support for these files. However, Team Edition for Database Professionals checks the files for syntax before building and deploying them.

Adding logins

The Logins.sql file will be added as a pre-deployment script file. This file will contain calls to CREATE LOGIN (or sp_addlogin, for backward-compatibility with SQL Server 2000) for SQL Server logins that support users who are added to the database. If you import a database schema, SQL Server logins from the source server will not be imported into your project. Only SQL Server logins that are explicitly required to support database users in the project will be added to this script file automatically. If additional SQL Server logins should be added to a server upon deployment, you should add them to this script file.

Adding linked servers

Linked servers will not be scripted by the import functionality of a database project. If you import a script that has scripts to create linked servers (sp_addlinkedserver), these calls are routed to the LinkedServers.sql file.

Importing and creating permissions

Permissions are routed to the Permissions.sql file when you import a schema or a script. These permissions include all grant, revoke, and deny permissions that exist either in your script or in the source database when you import a schema. This set also includes the CONNECT permission that enables database users in SQL Server 2005.

Creating certificates and keys

The EncryptionKeysandCertificates.sql pre-deployment file contains calls to create certificates, asymmetric keys, or symmetric keys. Additionally, you can use this file to create a database master key. Each of these object types typically has a password and is not reversible from the DesignDB instance. Therefore, you must manually add or modify these objects as part of this pre-deployment script. This file should contain the following statements.

CREATE MASTER KEY

CREATE CERTIFICATE

CREATE ASYMMETRIC KEY

CREATE SYMMETRIC KEY

If you import a schema that contains these objects, they will not be scripted, because Schema Compare does not support them. However, skeleton scripts or commented statements will be moved to the appropriate pre-deployment script file (EncryptionKeysandCertificates.sql).

Adding signatures

The post-deployment file Signatures.sql contains calls that add digital signatures to database objects by using the ADD SIGNATURE statement. If you import a schema that contains signatures, the signatures are not included. However, if you import a script that contains ADD SIGNATURE statements, those calls are moved to this post-deployment script file.

Security Considerations for Features with External Access

Many features of Team System for Database Professionals access external resources, such as databases. This section examines security considerations for these features and describes the permissions that you must have to use them.

Protecting Project Files on Disk

To protect your project from unauthorized access, you should use the NTFS file system and restrict permissions on the directories that store a project to the minimum set of users who need access. You might also consider using Encrypting File System (EFS) or some other encryption technique to protect project files locally. By default, projects are created in the drive:\\My Documents\Visual Studio 2005\Projects directory, which can be accessed only by the user who is currently logged on. If you change the default project directory, you should set similar permissions on the parent directory.

If you store these files in source control (highly recommended), you should consider restricting access to sensitive projects (or even sensitive files, such as those that contain passwords in a project) to the minimal set of users who must have access to those files.

Importing Schemas

SQL Server 2000

To import a schema from SQL Server 2000, you must have a valid database user for the database whose schema you want to import. Because SQL Server 2000 has no specific metadata permissions, all users of a database can read the schema definitions.

However, users cannot access objects such as stored procedures, triggers, views, and functions that have the WITH ENCRYPTION option included in their object definitions. If this option is included, the text for these objects is obfuscated in the syscomments system table and cannot be reverse-engineered from the source database. When the import operation encounters an encrypted object, a warning appears in the Error List window in Visual Studio, and the object is not scripted.

SQL Server 2005

In SQL Server 2005, the ability to view object definitions (known as metadata permissions) is not automatic. A user must have the VIEW DEFINITION permission on each object to see that object as eligible for import into a database project. Some database roles, such as db_owner, have this permission automatically granted to their members. In addition, object owners can always see the definitions of objects that they own.

All users of a database can always see the definitions for partition schemes, partition functions, file groups, and schemas.

Building and Deploying

When you use the build command, a script that represents the project is produced. This script is a .sql file under the project directory, which is the \sql directory, by default. You should protect this file, just as you would protect other files that might contain sensitive information about the database project. This script is a single file that represents all pre-deployment and post-deployment files put together, in addition to a script of all schema objects (or difference scripts if this deployment is to an existing database).

Permissions that you must have to deploy vary significantly, depending on what types of objects are being deployed. For example, team members who add SQL Server logins must have securityadmin permissions on the target server. You might need ownership of schema objects, db_ddladmin permissions, or perhaps even db_owner rights to deploy schema objects. As noted previously in the "Setting Permissions by Environment" section, you might need highly specialized permissions to deploy a database or database changes in a production environment.

Comparing Schemas

To compare schemas from SQL Server 2000 and SQL Server 2005, you need the same permissions as you need to import schemas. However, you must also have permissions to update the selected database if you want to write updates to a target.

Comparing Data

To compare the data between two tables or views, you need SELECT permissions on the selected objects to generate the script for comparing data. You need additional permissions (such as INSERT, UPDATE, and, in some cases, ALTER) for objects, depending on the data-comparison options that you specify.

Generating Data

To generate data, you need INSERT permissions to add data to the target database. You also need DELETE permissions if you specify that the targeted tables should be cleared of data. Additionally, you might need to disable insert and delete triggers so that data generation plans can succeed. You must manually disable these triggers on the target system by using a tool, such as SQL Server Management Studio, if they interfere with running the data generation plan. You also need the VIEW DEFINITION permission on the target server for all tables or views to be populated because a schema comparison is performed to verify whether the data generation plan is still valid for the target that you specified.

To reset an identity column on a table, you must be the object owner of the table or a member of the db_owner or db_ddladmin role.

Running Unit Tests

To run unit tests, you must have one connection to the target server to run the unit tests and a second connection to validate the test results. The validation connection might require additional permissions because it will need to access more schema objects and so forth to validate your test results. You can run these connections under separate security contexts, as long as you are using SQL Server Authentication, instead of Windows Authentication. If you are using Windows Authentication, both connections will run as the user who is logged on.

If you use SQL Server Authentication, you probably need a password. Team Edition for Database Professionals keeps this password encrypted in the registry and uses your Windows user account as the key. Therefore, other users who log into the same computer cannot access these passwords and must reenter the passwords if they are using SQL Server Authentication on database connections.

The nature of the unit test determines what permissions you must have to run that test on the target system. For example, if the unit test selects rows in a table, you must have SELECT permissions for that table. Additionally, if the test uses automatic build and deploy features, you must have administrative permissions on the target server to update and replace schema objects (as mentioned previously in this document).

Conclusion

Security in Microsoft Visual Studio 2005 Team Edition for Database Professionals takes many forms. The product supports a variety of connections to databases, in addition to security objects within database projects.

For additional information about Visual Studio security, see the Security Developer Center on MSDN. For additional information about SQL Server security, see the Security page for SQL Server. For general information about Visual Studio Team Edition for Database Professionals, see the Database Professional Team Center for Visual Studio Team System.

© Microsoft Corporation. All rights reserved.