Microsoft SQL Server history

Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations’ data infrastructure. As data has become increasingly valuable, the security of this infrastructure is more important than ever.

Because of this we’ll do a blog series about the different security features offered by Microsoft SQL Server.

Over the years, Microsoft has continuously improved the security features of SQL Server, responding to new threats and vulnerabilities with innovative solutions.

In this blog post we’ll start with a trip down memory lane and explore the history of security in Microsoft SQL Server, from its early days to the present. We’ll introduce the key security features that have been added, and the future of security in SQL Server.

In the next blogs you’ll read more about:

• SQL Server specific security
• Database specific security
• SQL Ledger
• SQL Injection
• SQL Server Auditing – Assessments
• Security differences Azure vs On-Prem

Once up on a time…

The first version of SQL Server, 1.0, did not have many security features. It only provided basic security features such as server-level security with usernames and passwords.

SQL Server 4.2 (1993): As I was hitting puberty, SQL Server was growing up little by little too and it started to gain a few more security features. It introduced a more robust security model based on Windows NT security (RIP Windows NT 3.1). This meant that users could be authenticated using their Windows credentials, and permissions could be granted or denied based on these credentials.

SQL Server 6.0 (1995): SQL Server entered adulthood here as it introduced several security features, laying the foundation of its (security) future. It introduced a new security feature called “Integrated Security,” which enabled users to authenticate using their Windows credentials. This provided better security than simple usernames and passwords. In addition to Windows authentication, SQL Server 6.0 introduced SQL Server logins. This allowed users to create and manage logins specific to SQL Server, providing an alternative to Windows authentication.  They also added Password Policy Enforcement, including password expiration, complexity requirements, and account lockouts. One other important introduction was the ability to use SSL for client-server communication.

Yeah, it deserved its first beer!

SQL Server 7.0 (1998): The version I started my SQL Server adventure with. This introduced a new security model called “Role-Based Security,” which allowed administrators to define roles and assign permissions to these roles. This made it easier to manage security at a more granular level.

SQL Server 2000 (2000): SQL Server 2000 enhanced password security by storing passwords as hashed values instead of plain text. It also introduced the ability to separate user schemas from system schemas, providing better isolation and security for database objects. It allowed users to create custom schemas and assign permissions to them, promoting a more secure and organized database structure. Not a security feature as such, but in this version an old friend was born: SQL Server Profiler. Ah, the nostalgia.

SQL Server 2005 (2005): This version was my first serious SQL Server relationship, not just a teenage fling. So many exciting features were introduced in this version, even though I did miss the DTS’ for a while. It introduced a new security feature called “Encryption,” which allowed administrators to encrypt data at rest and in transit. It also introduced a new security feature called “Schema-based Security,” which allowed administrators to define permissions based on database schemas. Another critical feature introduced in this version is that you had to create a strong sa password. The earliest versions of SQL Server, users were always prompted to set a password for the sa account during the installation or configuration process. The blank sa password issue typically occurred when users either chose not to set a password for the sa account during installation or accidentally (read: too lazy) left it blank. You also got the Surface Area Configuration tool, for tuning network connectivity options. Some other cool stuff that was introduced (being the first love and all), were: Database Mirroring, DMV’s, SSMS, … Sweet times.

SQL Server 2008 (2008): TDE came to life here, known as encrypting data at rest. TDE does real-time I/O encryption and decryption of data and log files. Also, SQL Server Audit, good for auditing/compliance reporting. Another cool new feature, though not widely used sadly enough (in my opinion), was Policy-Based Management. Ideal for standardizing your SQL Server environments. Ah, for those out there still running this version: time for an upgrade!

SQL Server 2012 (2012): This version introduced a new security feature called “Contained Databases,” which allowed administrators to define database-level security that is independent of the SQL Server instance. It also introduced a new security feature called “User-Defined Server Roles,” which allowed administrators to define custom server roles. Another important new option was to run your SQL Server services under Managed Service accounts (introduced in Windows Server 2008 R2). Managed Service Accounts, shortened as MSAs, have an automatically managed, complex password (240 characters) that removes the requirement of manually dealing with password rotation and security.

SQL Server 2014 (2014): Finally, “Backup Encryption” was introduced in this version, which allowed administrators to encrypt backups to protect them from unauthorized access.

SQL Server 2016 (2016): Like version 2005, the 2016 version also introduced several new exciting (security) features. This version introduced a new security feature called “Dynamic Data Masking” which allowed administrators to mask sensitive data without changing the actual data. Always encrypted was also released. It also introduced a new security feature called “Always Encrypted” which allowed administrators to encrypt sensitive data both in transit and at rest. Contrary of TDE, this does require more configuration on both server and application side, which prevented it to be setup easily in existing environments (not a “next -> next -> finish” kinda thing). Lastly, for security, Row-level security was launched, allowing administrators to define security policies that restrict access to specific rows in a table. This feature enables fine-grained access control and helps protect data by ensuring that users can only access the rows that are relevant to them.

SQL Server 2017 (2017): The focus of this version was more on performance rather than security enhancements. One new feature is the “Data Discovery and Classification”. SQL Server 2017 introduced built-in tools for data discovery and classification. These tools help identify and classify sensitive data within databases, making it easier to implement data protection and compliance measures.

SQL Server 2019 (2019): Focusing more and more on integration with Azure, SQL Server 2019 strengthened its integration with Azure services, providing enhanced security features such as Azure Key Vault integration for managing encryption keys, Azure Active Directory authentication for managing user identities, and Azure Data Studio integration for secure cross-platform database management.

SQL Server 2022 (2022): The latest/newest version of SQL Server. In this version Microsoft is focusing, obviously, on integration with Azure; but also, on being secure for cyber-attacks. SQL Server 2022 added support for Ledger technology. There’ll be a blog that goes deeper in this new feature. Though, to lift the curtain a little bit… Leger provides protection of the data from any attacker or high-privileged user, including database or system administrators, and helps streamline audits, providing cryptographic proof of data integrity to auditors. It does this by keeping a history of all transactions executed on the table and a hash of the transactions. This can be used to create immutable records of financial transactions or other activities that need to be kept secure and private. SQL Server 2022 introduces new access roles to help comply with the Principle of Least Privilege. For instance, members of the ##MS_LoginManager## fixed server roles are now able to create, delete and modify logins. This role is designed to be more limited in comparison to the previous  securityadmin, and it helps to promote better security practices by reducing the amount of privileges granted to members. Another feature is hypervisor security. This technology ensures that individual processes are isolated from the Windows Operating System. This is handy when your system is being attacked, making sure the threats are not spread to other parts of the system. Support for TLS 1.3 is also added in this version (do check ODBC and OLE DB version requirements).

Aaand, that brings us to today!
Decades of new or enhanced SQL Server security features later has led to a, in my opinion, mature product. Are there still things that can be improved? For sure, and with the age of AI at our doorstep, there will be plenty of (security) challenges thrown at us sooner than we think.

As I said some decades ago, there will be more blogs coming which will describe some security stuff in more detail, so stay tuned!

To finish with something AI, I asked ChatGPT to write a wrap up sentence. The result:

And now, it’s time for the grand finale! As we bid adieu, let’s sprinkle some stardust of security wisdom to fortify your SQL Server realm. Here are a few enchanting recommendations to weave an impenetrable shield around your data kingdom!

• Use strong passwords (duh): SQL Server login credentials should have strong passwords that are difficult to guess or crack. Passwords should be at least 8 characters long and contain a combination of upper and lower case letters, numbers, and special characters. Even better, try to use windows authentication only.
• Implement Managed Service Accounts (MSA) for your SQL Server services.
• Implement least privilege access: Grant SQL Server access only to users who need it to perform their job functions. This reduces the risk of unauthorized access or malicious activities by limiting the permissions of users and roles.
• Enable auditing and monitoring: Enable SQL Server auditing and monitoring features to track user activity and detect suspicious or anomalous behaviour. Review audit logs regularly to identify potential security issues.
• Use encryption: Use encryption to protect sensitive data stored in SQL Server databases. This includes using Transparent Data Encryption (TDE) for data at rest and Secure Sockets Layer (SSL) for data in transit.
• Keep SQL Server updated: Keep SQL Server up-to-date with the latest security patches and updates to address known vulnerabilities and security issues.
• Implement firewalls: Implement firewalls to restrict access to SQL Server from unauthorized or untrusted networks. This helps to prevent external attacks and limit the risk of exposure to SQL injection attacks.
• Conduct regular security assessments: Conduct regular security assessments and penetration testing to identify and remediate potential security vulnerabilities in the SQL Server environment.
• Limit surface area: Limit the surface area of SQL Server by disabling or removing unused features and services that are not required for the organization’s business needs. This reduces the risk of exposure to vulnerabilities and attacks.

Stay safe!

– Jan Vandenpanhuyzen, Kohera