1. ‘Is there a simple way of making my SQL Server data secure?’
2. ‘I still don’t really get what an endpoint is…’
3. ‘What is the ‘surface area’ of SQL Server?’
4. ‘Why do I need access control when my data is encrypted?’
5. ‘Why can’t the hacker just guess your password?’
6. ‘What is an alerting system? What makes a good alerting system?’
7. ‘What is a Principal and how is it different from a login?’
8. ‘So how do I get my database users to access only the data that I want them to see?’
9. ‘So what are Permissions and how does SQL Server work out if a user is allowed access to a securable?’
10. ‘Can’t I just use certificates to control access?’
11. ‘I use a security system that, I’m told, has no metadata security. Why is Metadata security an issue?’

1. ‘Is there a simple way of making my SQL Server data secure?’

SQL Server Security is best treated like the defenses of a fortification. This means “defense in depth”. The most secure SQL Server instances and databases are designed from the start with security in mind. Security cannot guarantee that an attacker with infinite time and patience can’t get to the data: security is the art of making it as difficult as possible, in as many ways as possible, so as to make an intrusion futile. Some precautions you use, such as defining non-default endpoints and restricting surface area, serve to slow down the determined attacker and deflect the opportunistic ones. Others, such as effective access control, can do a great deal to prevent access to data but rely on the attacker failing to take control of a database owner or sysadmin login.

Not only do you need to make life as awkward as possible for the attacker, but also to check obvious weaknesses. For example, are your backups secure as well? Are you giving administrative accounts to ordinary users? How are credentials stored by operational staff; are they exposed in plain-text in scripts? What are the privileges given to the account under which each service runs?

The final part of SQL Server Security is training. If you let anyone wander into your server room, or put the credentials or even the Azure address of a server on public GitHub, or if you pin your SQL password on the office wall, and forget you’ve done so when the local television station interviews your boss in the office, then you’ll render a lot of security work ineffective. However, if all of the other possible security precautions are in place, you still have time to slam the door shut.

2. ‘I still don’t really get what an endpoint is…’

Imagine that this is all about home security. Endpoints stop the intruder getting into the area easily. It is, I suppose, like the locked gate in a wall. You can specify who gets past and who can’t, and through what door. It is better to have as few doors as necessary, and have locks on them. It doesn’t prevent access, it just provides a nuisance to the interloper, and makes your home less visible.

Likewise, an endpoint is a way into SQL Server. You can govern who gets through each endpoint. Endpoints are similar, in principle, to a firewall, though they only govern inbound traffic. Together with a router, you can define not only who has access but also from what address-range. A number of endpoints will be set up by default, but you can remove the ones you don’t want and add others. If you’ve ever had a SQL Server exposed to the internet, and examined the traffic on the network, you will see that there are constant automated probes to check the customary default endpoints to see if there is any prospect of intrusion. By configuring endpoints intelligently, along with routers, you can eliminate this traffic. In this way, endpoints help to make your data less detectable to unauthenticated users, and can deter opportunistic intruders who are using TCP port scans, UDP port scans (like a sort of SQL ‘ping’), UDP broadcasts or Azure DNS dictionary attacks to discover SQL server instances.

Endpoints are of three basic types, the general-purpose TCP and the special Service Broker and Database Mirroring types. Endpoints are associated with particular network transport protocols, such as TCP, or HTTP. You also specify a payload, which is one of TSQL, Service Broker, or Database Mirroring. There was a SOAP transport protocol but it is now deprecated.

Endpoints are configured to listen on the port number for the instance. The public role is given access. This means that endpoints are an obvious place to start in tightening up security. For example, every potential intruder knows that 5022 is the default port number for Database Mirroring and 1433 is the default TCP port for a TSQL endpoint. You should force the hackers to use a port scanner, which should discourage the many probes that just test 1433 and 5022. If you wish to restrict access to a particular IP address for accessing SQL Server, assuming the server has several, you can tie that down too.

When creating your own endpoints, you will assign a different IP address to each one, and in this way you can partition your users to different ports or addresses, through the firewall, to make network admin and audit easier. You could create a different TCP endpoint for each application, and give only that application the rights to use that endpoint. Each application accessing the database would use a different port for connecting to the server, using the connection string.

When you have created and defined the endpoint, you can then grant, revoke or deny access to database users, groups, roles, and logins. This means that even if you have valid endpoints that a potential intruder discovers, he cannot access the system unless his login is granted access and is granted CONNECT permission on the endpoint. Even if the has this, he cannot use the system unless the endpoint is started (they can be stopped, returning an error message to a request, or disabled, not responding to a request).

When creating and configuring endpoints, consider that different payloads have different properties. For the Database Mirroring payload, you’d want to select the mirroring role and whether encryption is enabled: For the Service Broker payload, you need to define message forwarding, the maximum number of connections allowed and the authentication mode. Protocols too have properties. The HTTP Protocol allows you to select a list of restricted IP addresses, and specify the port, website, and authentication information. The TCP protocol object also requires a list of IP addresses that SQL Server can use, and port information.

For Mirroring and Service Broker, it is better to use Windows Authentication if possible, otherwise go for certificate-based authentication. You should use a reasonably strong algorithm for encryption such as Advanced Encryption algorithm. (AES) but there is a range to choose from. For Mirroring, you have only one endpoint in the instance.

For creating and maintaining endpoints, you have several options. You can do it via SQL DDL statements, SMO also does it  all, and even generates the SQL Scripts. For reporting on endpoints,there are the catalog views such as sys.endpoints, sys.tcp_endpoints, sys.http_endpoints, sys.database_mirroring_endpoints and sys.service_broker_endpoints. If you have time to kill, you can use SSMS. I give a lot more details my previous article on Endpoints.

3. ‘What is the ‘surface area’ of SQL Server?’

There was a time when every instance of SQL Server was installed with all the network features and services enabled, even if the user didn’t want or need them, and such unused features are merely a security risk. This was particularly so if the IDs and passwords are weak.

Nowadays, you’ll hear the term ‘Surface Area Configuration’ and all this means is that SQL Server is no longer installed by default with all its features enabled; only the key services and features are installed by default. The more that are enabled, the bigger the ‘surface area’ exposed to intruder attack. Minimizing the surface area is not really a security feature but it helps to reduce the risk.

The system administrator can decide at installation time what these key features and services are, but it is also possible remove or add them in an active instance. Typical of the sort of features that are often disabled if they are not needed are XP_CMDSHELL, OLE automation, OPENROWSET and OPENDATASET. Only the essential endpoints are provided, so some components may not be available remotely until suitable protocols are added that can be used by ‘custom’ endpoints. Once everything you need is installed, the experienced DBA need look no further than sp_configure to enable or disable features, but there are several other ways. For a while, back with SQL Server 2005, there was a special Surface Area Configuration tool, now morphed into the SQL Server Configuration Manager. Since the introduction of Policy Based Management, this is probably the easiest way of managing the surface area of a number of instances. If you have only a small number of instances to look after, you can use the Surface Area Configuration Facet in SQL Server Management Studio. The automation freaks use PowerShell and SMO, but it can be scripted just as well purely in SQL.

4. ‘Why do I need access control when my data is encrypted?’

Even if you have a safe in your house, you still lock the doors when you go out. Data Encryption should be considered as just another security layer. It is good, but not in itself sufficient. Many argue that it has nothing to do with security, but like Endpoints, it just makes things more tiresome for the intruder.

Encryption only helps to limit data loss if access controls have been bypassed. It is not a substitute for access control systems. All sensitive data should be encrypted, of course, under the principle of defense in depth, but if an intruder has gained access to the table containing the data, there is already something very wrong with the security of the database.

The problem is that the development of secure encryption algorithms goes hand in hand with the development of cracks. Security algorithms are no more immune from weakness than programs are free of bugs, and if an algorithm is cracked, you may not know until years later. You also have backdoor exploits, private key carelessness, and a whole range of other ways around your encryption.

5. ‘Why can’t the hacker just guess your password?’

Many hackers do. If the intruder manages to find or guess the password sysadmin login, then it is likely to be game over. Most successful SQL Server security breaches are done purely by a dictionary attack on the SA (administrative account) password. After all, how hard is it to guess ‘Password1234’?

Some time ago, on installation SQL Server used to default to SA with no password, so it didn’t take much guessing. Even today, some instances of SQL Server Express are installed with applications and have default passwords the same as the instance name, unless some alert admin has changed it. To make dictionary attacks easier, the SA credentials, unlike ordinary logins, can be offered an unlimited number of login attempts without the connection being shut down.

The first precaution is, therefore, to change the name of the SA login name to something harder to guess, ALTER LOGIN sa WITH NAME =obscure_login_name, and give it a password that is long and machine-generated. Do not pin it on a noticeboard. Use an encrypted connection to log into SQL Server.

The second precaution is to use a Windows Authenticated sysadmin login instead of SQL Server authentication credentials. The only downside to this is that all the SQL Server instances within the domain are visible to domain users.

The third precaution is to have an intrusion/threat detection system that at least monitors failed logins. On Azure, Azure SQL Database Auditing will help but the most important aspect to start with is a good alerting system.

The fourth precaution is to avoid using mixed SQL Server security because it is relatively easy for an attacker to trick you into executing a script when you are using your DBA windows login. This script would, unknown to you, add a SQL Server SysAdmin login to your server. (For obvious details I won’t give details, but you should read this as a starting point)  You’ll know immediately if you have a good alerting system, of course, but is yours that good?

6. ‘What is an alerting system? What makes a good alerting system?’

If you have a farm, it is wise to have a guard dog that barks loudly when it sees something suspicious. Likewise, as part of a good system, the network admins should have an intrusion detection system in place. The SQL Server logs should also be monitored to detect a brute-force attack, which will result in a high number of failed login attempts. Any changes to the DDL should be detected and logged via the default trace, or extended events, and anything suspicious should be alerted to the DBA. There should be an active antivirus scan on the server that runs continuously to detect a payload such as a keylogger being added, though this requires cautious administration. Any payload that is added can then be deleted or quarantined before the attack can compromise any data.

7. ‘What is a Principal and how is it different from a login?’

A ‘Principal’ is just something that can request SQL Server resources. It is a collective noun for the various types of logins, groups, roles and users. It can refer to a single individual user or a collection, such as a role. You just use the term ‘principal’ instead when referring to something that applies to them all.  Basically, a principal is the name given to anything to which you can assign permissions. In the same way, a securable is anything to which you can restrict access, such as a database or a table. In fact, securables can’t be accessed until permission is granted to access them. You can GRANT, REVOKE, or DENY permissions ON a securable TO a principal.

Logins

If a SQL Server instance was a house, a login would get you through the front door and into the hallway but not into any of the rooms or apartments. A login is a type of principal. It is used as the means of gaining access to an instance of SQL Server, but doesn’t by itself get you into a database. Login authentication happens at the server level, not the database level. A login can be a SQL Server ID/Password login, a Windows User authentication or a Windows Group authentication. Logins can be a collection or groups of users as well as individuals. A login mapped to a certificate, or an asymmetric key is an exception in that it cannot be used to gain access to an instance, and is just used as a security container.

Generally, Windows Groups are the best choice because the domain admin can assign specific server permissions to that group, and administer who is in the group merely by adding or removing Windows users from that group in the Active Domain, as part of a standard business procedure.

If for some reason you can’t use windows authentication, then you’ll need to use SQL Server logins. These are protected from brute-force attacks except for SA, so remember to rename this login and assign a strong password, as described previously.

Server-level roles make it quicker and easier to manage server permissions. These roles are server-wide in scope and are actually security principals that group other principals. These nine roles can’t be changed but in SQL Server 2012 onwards it is possible to create user-defined server roles that allow you to add server-level permissions to user-defined server roles. You, as system administrator, can add other server-level principals (SQL Server logins, Windows accounts, and Windows groups) into server-level roles and members of a fixed server role can add other logins to that same role. Members of user-defined server roles cannot add other server principals to the role.

To be able to access database resources, the login must be tied to a specific database user principal, which is then granted the permissions necessary to access the database objects.

Users

Users are different from Logins. Logins provide a means of authenticating anyone accessing the instance. Users are stored for individual databases and control permissions for activities performed on that database.

Users of a database are generally principals, but not always. There are two entities called INFORMATION_SCHEMA and sys that are listed as users. However, they are not principals, and they cannot be modified or dropped. They are required by SQL Server.

Users generally map to Logins via their SID, and usually default to a particular schema. Users can be mapped to a certificate or asymmetric key but such a user doesn’t allow a login to gain access to the database. Loginless users replace application roles: application roles couldn’t be easily audited because it was impossible to identify the individual using the role. Real users use their logins to access the server and then they impersonate loginless users to gain the necessary permissions, but can still be identified.

8. ‘So how do I get my database users to access only the data that I want them to see?’

By default, your database users won’t be able to see anything until you permit it, unless they’ve been foolishly assigned to the built-in server level administrative role, sysadmin, or the built-in database level administrative role, db_owner. A member of the former maps to the dbo user in every database, so is automatically a member of the db_owner role. A login assigned to one of these roles has unlimited powers to see and alter everything. SQL Server does not check permissions on an administrative account, so you can’t even remove permissions from them.

Rule number one: never assign normal logins or users to these administrative roles. You need to take a subtler approach that retains the principle that every securable is off-limits unless you decide to give them permission to access it, but yet makes things manageable. Database roles provide part of the solution.

Database Roles

The built-in database roles can be supplemented by user-defined database roles that work in the same way. You can assign each different type of database user to a user-defined database role and then grant each role the appropriate permissions.

If your users represent windows group logins, then the membership of a user role can be made to correspond with a windows group, effectively allowing the windows administrator to control which people given are access to the database, within the organization. There should be sufficient database roles to allow you to allocate to every type of user of the database just enough privileges to do their work but no more than that. A user can be assigned to several roles if necessary, and as many users as necessary can be added to each role.

The other part of the solution is to use schemas as intended, as a way of managing access.

Schemas

Instead of applying permissions to individual objects, you group all the objects that are intended to be accessed by a particular database role, and place them in the one schema. By assigning a permission to a schema, you apply that permission to all objects within the schema, unless this permission is then explicitly denied at object-level. That role can gain access to the data in base tables by means of a view or function that allows access to only the appropriate data from that base table. By ensuring that the view or function is owned by the same owner as the base table, we ensure that he principal will be able to use the view, but not the base table. Ownership-chaining keeps everything neat. This security design can make it difficult for SQL injection but it is still possible if ‘dynamic SQL’ is used carelessly in stored procedures.

By using schema-based security and ownership chaining, applications can be designed that use procedures and functions to accomplish all the data manipulation that the application requires. Users are never granted access to the base tables and the only actions that can be taken by the user are those determined by the routines that comprise the application interface. This type of interfaces represents best-practice in application design. With such an interface, the dependencies are reduced to the extent that development of the database can be done independently of the application.

9. ‘So what are Permissions and how does SQL Server work out if a user is allowed access to a securable?’

Every SQL Server securable has associated permissions that can be granted to a principal. Securables range from coarse-grained, at the server-level and instance-level, down through to database-level and then schema-level and finally to the most fine-grained, which is individual objects, such as tables and routines within a database. Even the metadata that contains the system catalog is securable.

The sheer range of securables in SQL Server means that the range of permissions can become daunting. By default, permissions granted to a securable will apply to all objects contained within it. Permissions at the database level will affect all objects within the database, and permissions on a schema will affect all the objects within that schema.

Some permissions, such as CONTROL, TAKE OWNERSHIP or VIEW DEFINITION, can be assigned to almost all securables, whereas others, like ALTER TRACE, apply only in one context. You can GRANT or DENY a permission and a DENY overrides a GRANT. The REVOKE will remove both a GRANT or DENY.

An explicit permission is one that is assigned via a GRANT, GRANT WITH GRANT, or DENY; A permission is implicit if it is implied by a more general permission. The CONTROL permission on a schema is inherited by all objects within the schema and will imply SELECT on tables and views.

Permissions can apply to a SQL server table or view, synonym, and column to affect whether SQL statements such as INSERT, UPDATE or SELECT can be executed on that securable. It can get confusing to work out what of permission is required when faced with, say, a table that needs to be accessed: INSERT, UPDATE, DELETE, SELECT are fairly obvious but what about EXECUTE, ALTER or CONTROL? (CONTROL gives ‘owner-like’ privileges to the grantee, ALTER allows the grantee to change the properties, except ownership, of the securable, and EXECUTE allows the grantee to execute procedures, functions, CLR types, scripts and synonyms) What about a Stored Procedure that is inserting into several tables? The answer is that you’d need both EXECUTE permission on the stored procedure and INSERT permission on the tables that it references.

Here, in brief, is how SQL Server works out if it should permit a request to access a securable:

• Member of Admin role? Is the login a member of the sysadmin fixed server role or db_owner fixed database role? If so, then allow access and skip all further checks.
• Ownership chaining? If access to the object is via a routine such as a view or function, and the access check on that object earlier in the chain passed the security check, then allow access.
• Does the security context contain the required permissions?
  • Work out the security context of the user by aggregating the whole set of principals (logins, roles, groups) of which the user is a member, and therefore contribute permissions to the access check.
  • Identify the required permission and compare to the permissions within the security context of the user
  • Is permission denied? If any of the roles, or other memberships, to which the user is assigned, is denied permission to the objects, or containing objects, being examined, then permission is denied
  • If not denied, is permission granted? Check for a GRANT or a GRANT WITH GRANT permission, either directly or implicitly, to the objects, and containing objects, being examined. If it exists, grant permission to access.

SQL Server prevents you from granting, denying, or revoking permissions to sa, dbo, the owner of the securable, information_schema, sys, or yourself

10. ‘Can’t I just use certificates to control access?’

X.509 Certificates are shared between two servers to allow them to be very sure of who is at the other end of the conversation via strong authentication. They are a good way of ensuring that processes such as mirroring cannot be hijacked. They are used in specialized endpoints for mirroring but they, like asymmetric keys, cannot be used for user authentication.

11. ‘I use a security system that, I’m told, has no metadata security. Why is Metadata security an issue?’

Once an intruder is into a system, using a SQL Injection exploit, then unless you’ve secured the metadata, then he or she can determine the names of tables, for example, or see the source of routines such as functions or procedures. It makes the hacking process easier and far less easy to detect. The intruder that can’t easily find out the names of tables, functions and procedures, will have to do it the hard way by deliberately causing errors, and that should draw the attention of your alerting system.

For effective security, no users should be able to view any information about objects to which they have no permissions. By default, this is the way that the system works. If you make SQL queries to the metadata, you will only see the tables, and so on, to which you have permissions: The same is true of Management Studio. You can only see those things in the object explorer that you have permission to see. It is possible to devise a way of controlling access that allows view access, or even to do it by accident, but you lose the advantages of metadata security, so the answer is that each user of the production database should only have permission to view the objects that they actually require. Then all will be well.

Conclusions

It is a bad idea to leave any instance of SQL Server insecure, even if it is just on your laptop. It is not just the data that needs protecting but the platform and the network. By gaining access to a machine in the network, an intruder has a foothold on the network.  A public-facing server based in the cloud that is serving applications will require a whole range of ways of minimizing the risk of intrusion, and the subsequent damage an intruder can wreak. This requires defense in depth, where an attack is made as tiresome and time-consuming as possible for a variety of reasons. It also requires that any attempt at intrusion triggers an alert, allowing the admin to intervene where possible. You don’t have alternatives in SQL Server Security and access control, you have to adopt all the devices that are available to you.