Once a SQL Server instance has been configured to allow remote connections, it is also necessary to configure the instance’s security to provide access for authorised users.
The highest level of access – providing full unlimited access to the server – is for a user to have the ‘sysamin’ server role. It is not recommended that this role is given to standard users except where strictly necessary. However, sysadmin privileges are required for certain one-off initialisation tasks, although these can be performed by an appropriate sysadmin-level user.
SQL Server security is provided by granting roles and permissions to logins and users.
Note that the T-SQL statements that follow should be executed within the SQL Server Management Studio by a login with the specified server role (alternatively, please refer to Appendix B – Screenshots).
Each SQL Server instance maintains a list of logins. These are accounts through which a user connects to a SQL Server instance. A login can be a Windows domain user (domain\username), or it can be a SQL Server user (username and password, for example the ‘sa’ account).
As Microsoft recommends, “It is a best practice to use only Windows logins whenever possible” rather than a username and password combination. matchIT SQL configuration files specify connection strings to SQL Server instances, so using a password can be a potential security concern if configuration files are not protected correctly. (matchIT SQL does allow for encryption of connection strings, but please be aware that this provides a low level of security that isn’t 100% secure.)
Logins can be assigned server roles (such as ‘sysadmin’ and ‘bulkadmin’) and permissions (such as ‘Control server’, ‘Create any database’, and ‘Unsafe assembly’).
To create a new login, use either of the following T‑SQL statements:
CREATE LOGIN [domain\username] FROM WINDOWS
CREATE LOGIN [username] WITH PASSWORD=’password’
Note that in all the following T‑SQL statements, domain\username can be used in place of username.
To assign a server role to the login, use the following::
EXEC sp_addsrvrolemember ‘username’, ‘role’
where role can be sysadmin, securityadmin, etc.
Note that a role can only be assigned if the login executing sp_addsrvrolemember is a sysadmin or has the server role being assigned to the target login.
* or 2008 or 2008 R2 if either of these versions is installed
 “Microsoft SQL Server 2005 Security Best Practices”. Bob Beauchemin, SQLskills. 2007.
Each SQL Server database maintains a list of users. These are SQL Server logins that have been given access to a particular database (a login can be given access to multiple databases).
Database users can be assigned database roles (such as ‘db_owner’) and permissions (such as ‘Control’, ‘Insert’, ‘Delete’, and ‘Update’).
To create a new user in a specific database, use the following T‑SQL statements:
CREATE USER [username] WITH DEFAULT_SCHEMA=dbo
To assign a database role to the user, use the following:
EXEC sp_addrolemember ‘role’, ‘username’
where role can be db_owner, db_ddladmin etc.
When accessing a database that isn’t hosting the stored procedures, a standard connection string is used that specifies the authentication method that will be used.
If Windows authentication is used (i.e. the connection string specifies “Integrated Security=SSPI”), then the user executing the stored procedure will be used to access the data.
If SQL Server authentication is used (i.e. the connection string specifies a user id and password), then that specific SQL Server login will be used to access the data. In this case, the stored procedures must be executed by a login that has execution privileges, but data will be accessed by a login that has the relevant data access privileges (the same login can be used for both execution and for data access).
As per data access on a local machine, either Windows authentication or SQL Server authentication can be used.
To access remote data using Windows authentication, either of these requirements must be met (note that instance refers to the SQL Server instance on which the stored procedures are hosted):
- the instance is running as a domain account, and the account has been given appropriate access to the database;
- the instance is running as a service account (such as Network Service or Local System), and the computer is given appropriate access to the database;
- the instance is running as a service account (such as Network Service or Local System), impersonation is enabled (see below), and the impersonated account has been given appropriate access to the database.
When running a matchIT SQL stored procedure from the SQL Server Management Studio, the stored procedure executes using the account that the SQL Server instance is running as.
For example, if the instance’s service is running using the computer’s Network Service account, then the stored procedure will run using that account; if a connection string (in a stored procedure configuration file) uses Windows authentication, then the computer will require access to the remote database.
matchIT SQL, therefore, allows the stored procedures to impersonate the actual user account that’s executing the stored procedure. This account will be used only when connecting to a database.
To enable impersonation, simply set the impersonation node (in the generalSettings block of a stored procedure configuration file) to ‘true’:
<impersonation enabled=”true” />
Note that impersonation isn’t necessary when:
- only local data access is performed; or
- the connection string uses SQL Server authentication (username and password); or
- the stored procedure is executed, for example, from a .NET application connecting to the database hosting the stored procedures; in such a case, the stored procedure will execute with the user account that’s running the application.