SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.
Microsoft has announced the latest release of SQL Server Management Studio (SSMS) in October. This release features improved compatibility with previous versions of SQL Server and a stand-alone web installer.
Get it here:
Continue reading “Free Download: SQL Server Management Studio 17.6”
If you have read the PCI DSS and the requirements for how you must store credit card data, you may be asking for some basic guidance for how to handle credit card numbers in your database systems.
These suggestions cover the basics – the full topic of protecting card data is easily several hundred pages long. These are basic ideas, but you should consult with your compliance team for final guidance.
Continue reading “PCI DSS – Storing Credit Card Numbers”
One of the hardest thing to do is prevent something from happening when you don’t know when it might happen or who will try to make it happen. As a Database Administrator, you have to be aware that a data breach might happen and you must take reasonable precautions to prevent them. According to the 2016 study by IBM, 60% of database attacks are insiders (people using approved network credentials) looking to access or steal corporate data.
There are some basic steps you should execute to help prevent unauthorized access to your database environment.
- Enforce Privileges – As an employee starts their tenure at a company, they are usually given the exact correct privileges for their position. The longer the employee is with a company, the correct privileges start to vary from the effective privileges, until eventually the employee has the wrong access privileges. You need to make sure those initial access rights are correct from day one, and that you periodically review the access rights for every employee. If there is any question about the correct privileges, you should contact their supervisor and document the correct level of access.
- Database Discovery – People are busy, and don’t always pay attention when new database instances are created. The people who manage the databases are often times not the people who install the software, so this can lead to an environment where there are unauthorized or poorly configured database instances. Database discovery is a crucial first step for avoiding security issues, so you should scan your environment for new database instances as often as possible. The amount of change in your environment will dictate how often you should search for new database instances, but the minimum is annually.
- Connection Encryption – Encrypting the connection between the user and the database can help prevent man-in-the-middle attacks.
- Strong Password – You should expect the same password strength for your databases as you expect on the network. If possible, use Windows Authentication instead of SQL Server Authentication. This will help enforce the same password strength as your network password, and you must verify that the network settings are using best practice strength requirements.
- Detect Compromised Credentials – It is estimated that 60% of companies cannot detect compromised credentials, based on a study by solution vendor Rapid7. Since authorized individuals use databases in a predictable way, abnormal or unauthorized access will be detected and you can be alerted. There are security appliances that can catch unusual or unwanted user access based solely on algorithm analysis, preventing a possible data breach.
Some people believe that their computer systems are more secure if the person attacking their systems don’t know some facts, like what port their SQL Server instance is using or by not disclosing the written specifications for critical software functions. Those people believe that if malicious attackers don’t know how the system is secured, security will be better. Although this might seem logical, it’s actually easy to see how it is untrue if you think about if for a few minutes. Insider attacks by employees, one of the most common forms of an attack, will already know the port used or how your software works.
The problem with security through ignorance is it just leads to a false sense of security, which is usually much more dangerous than not doing anything at all. Assume you are working with an intelligent attacker, and that your weak half-attempts to secure your systems will delay the attacker all of about 2 minutes. Spend your time and effort for implementing true security measures and you will sleep better each night.
This month, Microsoft introduced the public preview of a new database offering named “Azure SQL Database Managed Instance.” This new Azure offering is intended to create a database solution that more closely matches a traditional on-premise product, while supporting some advanced Azure features.
The Azure SQL Database Managed Instance feature was created to make it easier for users to migrate their existing third-party applications from an on-premise SQL Server instance to Azure by maintaining feature compatibility. This current preview version is not yet at the 100% complete mark, but Microsoft is promising some additional features in the coming months.
In terms of programmability and feature compatibility, Managed Instance supports compatibility all the way back to SQL Server 2008. It also allows for direct migration of database versions starting with SQL Server 2005. You can copy your on-premise backups to Azure (or backup directly to Azure) and restore them into the service seamlessly using Direct migration. Other features Microsoft has enabled include service broker, change data capture and linked servers, which had previously been limiters for moving on-premises applications into Azure SQL Database.
Continue reading “Azure SQL Database Managed Instance in Public Preview”
Spectre and Meltdown Vulnerabilities
Modern CPUs that use of speculative execution has led to the vulnerabilities now known as Spectre and Meltdown. “Speculative Execution” means that if the CPU is processing instructions A, B and C, it might execute instruction B before it has the results of instruction A. This vulnerabilities potentially allows malicious software to read what should be protected memory on an unprotected computer system, allowing access to sensitive data such as passwords, encryption keys, or even cached data like the SQL Server buffer pool. Since speculative execution is intended to boost CPU performance, removing this feature will likely impact SQL Server performance by making queries run as much as 30% slower.
Continue reading “Spectre and Meltdown Impact on SQL Server”
To understand SQL Server index fragmentation, you have to first understand how SQL Server stores data. You may already know that SQL Server stores data on 8KB data pages, and that eight contiguous data pages form an extent. Each data page in both in clustered or non-clustered indexes contains data and pointers to the next and previous pages.
As data is added to a table, the index data must be saved to a data page. If there isn’t enough room on an existing page, the SQL Server engine will create a new data page to store the data, but it must also adjust the pointers on the existing data pages to reflect the new sequence of the impacted data pages. This also means that a new extent might have to be created. These new objects may be stored in a completely different part of the storage drive. This causes both database and disk fragmentation, which can make performance an issue over time.
Continue reading “Understanding SQL Server Index Fragmentation”