There are a lot of data breaches these days due to poorly secured and ill-managed database servers. Microsoft SQL servers are very popular and provide most of the solutions you need, but at the same time, can be complex to configure and understand. Security is a top priority in every database management. Once you know the basic security configurations on your database, it will be easier for you when it comes to Fixing errors on SQL Server. In this article, we’ll share some critical SQL server security basics which every database admin needs to understand.
Windows Authentication Vs. SQL Server Authentication
First, you need to understand that Microsoft SQL servers support two authentication options and they’re;
- Windows Authentication; it relies on AD (Active Directory) to authenticate users before connecting to SQL. Active Directory provides better password management policies, and that’s why it’s the recommended authentication mode for users. It also provides better user and group management for access to applications in an organization.
- SQL server authentication; this involves using usernames and passwords on any database server, and in most cases, it’s used when AD isn’t available.
It’s strongly recommended to use windows authentication exclusively if the situation warrants. You should also note that you can use both Windows Authentication and SQL server authentication together; it’s called mixed mode.
If you have to use SQL Server Authentication, you should either make your default sa account has a secure password, which should be changed frequently or disable it. This is the account that’s mostly targeted by hackers. SQL Server accounts together with sa if enabled, can be managed by using SQL server management studio service or Alter-Login Transact-SQL command.
Server Logins And Roles
Whatever authentication you’re going to use, you can configure two logins to configure SQL instances, server logins, and user logins.
Server logins allow users to connect to the SQL server instance, and every server login is given 1 or 2 server roles that will enable it to perform a particular action on the case. Server logins are assigned public server role by default; this provides basic access to an instance. Other available parts include Sercurityadmin, Bulkadmin, Server admin, and Dbcreator.
On the other hand, before creating a user login, you need to specify a few things, first is the database that will be associated, and then the username and default schema to be applied for all objects which the user creates if there’s no other schema that’s specified. SQL server schemas are a collection of objects such as views and tables.
Data encryption
The following are encryptions supported by SQL servers;
- Secure Socket Layer (SSL); encrypts traffic when it travels between a server instance & client application. An example is how internet traffic is encrypted between a server and a browser.
- Transparent Data encryption (TDE); this is used to encrypt data on a disk, it encrypts data and log files.
- Backup Encryption; it’s similar to Transparent Data Encryption; it encrypts SQL backups rather than log files and current data.
- Cell level encryption/column; it ensures that data is encrypted while it’s stored in memory.
- Always encrypted; this is a development on cell-level encryption/column as it doesn’t need changes to client applications. The data will remain encrypted over the network, on disk or in memory. It’s also used to protect sensitive data from other privileged SQL server users.
Row Level Security
Row-level security (RLS); it lets organizations control who can view rows in any database. For example, users can be restricted from viewing rows containing information about clients. Row-level security includes three main parts, namely, predicate function, security predicate, and security policy.
These are some of the few basics every database admin should know to ensure their database is secured. There a lot of things to learn out there when it comes to securing databases; the few tips mentioned are just the tip of the iceberg. As a database admin, you should do as much as you can to ensure your servers.
