Thursday, August 12, 2021

SQL Script to list out name of tables and its records in the database

SET NOCOUNT ON 
DBCC UPDATEUSAGE(0) 
-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
CREATE TABLE #temptable 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
 
INSERT  #temptable  EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 
SELECT *
FROM    #temptable  order by CAST([rows] AS int) desc

-- # of rows.

-- SELECT SUM(CAST([rows] AS int)) AS [rows]
-- FROM    #temptable  order by [rows]

DROP TABLE #temptable

Result



SPARSE Column in SQL Server

A SPARSE column is a special type of column that has optimised storage for NULL values. It also reduces the space requirements for null values at the cost of more overhead to retrieve non-null values. In other words, a SPARSE column is better at managing NULL and ZERO values in SQL Server. It does not store any space in the database. Using a SPARSE column we may save up to 40% of space. We can define a column as a keyboard SPARSE column using the CREATE TABLE or ALTER TABLE commands.

Syntax

CREATE TABLE TableName
(
      .....
      Col1 INT SPARSE,
      Col2 VARCHAR(100) SPARSE,
      Col3 DateTime SPARSE
      
.....
)


We may also add / change a column from graphical view.



Example

In this example I have create two tables with the same number of columns and the same data type but one table's columns are created as a SPARSE column. both tables contains 717 rows individuals.

CREATE TABLE TableName
(
      Col1 INT SPARSE,
      Col2 VARCHAR(100) SPARSE,
      Col3 DateTime SPARSE
)

CREATE TABLE TableName1
(
      Col1 INT ,
      Col2 VARCHAR(100) ,
      Col3 DateTime
)

Using the sp_spaceused stored procedure we can determine the space occupied by the table data.

sp_spaceused 'TableName'
GO
sp_spaceused 'TableName1'

RESULT


Advantages of a SPARSE column

  • A SPARSE column saves database space when there is zero or null values in the database.
  • INSERT, UPDATE, and DELETE statements can reference the SPARSE columns by name.
  • We can get more benefit of Filtered indexes on a SPARSE column.
  • We can use SPARSE columns with change tracking and change data capture.

Limitations of a SPARSE column

  • A SPARSE column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.
  • A SPARSE column cannot be data types like text, ntext, image, timestamp, user-defined data type, geometry, or geography.
  • It cannot have a default value and bounded-to rule.
  • A SPARSE column cannot be part of a clustered index or a unique primary key index and partition key of a clustered index or heap.
  • Merge replication does not support SPARSE columns.
  • The SPARSE property of a column is not preserved when the table is copied.

Conclusion

SPARSE column is a good feature of SQL Server. It helps us to reduce the space requirements for null values. Using a SPARSE column we may save up to 40 % of space.

Tuesday, August 21, 2018

Hacker’s activities/Malicious user may try to hack


1. Direct connections via the Internet
These connections can be used to attach to SQL Servers sitting naked without firewall protection for the entire world to see (and access). I don't understand the logic behind making a critical server like this directly accessible from the Internet, but I still find this flaw in my assessments, and we all remember the effect the SQL Slammer worm had on so many vulnerable SQL Server systems. Nevertheless, these direct attacks can lead to denial of service, buffer overflows and more.
2. Vulnerability scanning
Vulnerability scanning often reveals weaknesses in the underlying OS, the Web application or the database system itself. Anything from missing SQL Server patches to Internet Information Services (IIS) configuration weaknesses to SNMP exploits can be uncovered by attackers and lead to database server compromise. The bad guys may use open source, home-grown or commercial tools. Some are even savvy enough to carry out their hacks manually from a command prompt. In the interest of time (and minimal wheel spinning), I recommend using commercial vulnerability assessment tools like QualysGuard from Qualys Inc. (for general scanning), WebInspectfrom SPI Dynamics (for Web application scanning) and Next Generation Security Software Ltd.'s NGSSquirrel for SQL Server (for database-specific scanning). They're easy to use, offer the most comprehensive assessment and, in turn, provide the best results. Figure 1 shows some SQL injection vulnerabilities you may be able to uncover.

Figure 1: Common SQL injection vulnerabilities found using WebInspect.
3. Enumerating the SQL Server Resolution Service
Running on UDP port 1434, this allows you to find hidden database instances and probe deeper into the system. Chip Andrews' SQLPing v 2.5 is a great tool to use to look for SQL Server system(s) and determine version numbers (somewhat). This works even if your SQL Server instances aren't listening on the default ports. Also, a buffer overflow can occur when an overly long request for SQL Servers is sent to the broadcast address for UDP port 1434.
4. Direct-exploit attacks 
Direct attacks using tools (any), shown in Figure 2, and its commercial equivalents (CANVAS and CORE IMPACT) are used to exploit certain vulnerabilities found during normal vulnerability scanning. This is typically the silver-bullet hack for attackers penetrating a system and performing code injection or gaining unauthorized command-line access.

Figure 2: SQL Server vulnerability exploitable using Metasploit's MSFConsole.
5. SQL injection
SQL injection attacks are executed via front-end Web applications that don't properly validate user input. Malformed SQL queries, including SQL commands, can be inserted directly into Web URLs and return informative errors, commands being executed and more. These attacks can be carried out manually -- if you have a lot of time. Once I discover that a server has a potential SQL injection vulnerability, I prefer to perform the follow-through using an automated tool, such as SPI Dynamics' SQL Injector
.
Figure 3: SPI Dynamics' SQL Injector tool automates the SQL injection process.
6. Blind SQL injection
These attacks go about exploiting Web applications and back-end SQL Servers in the same basic fashion as a standard SQL injection. The big difference is that the attacker doesn't receive feedback from the Web server in the form of returned error messages. Such an attack is even slower than standard SQL injection given the guesswork involved. You need a good tool for this situation, and that's where Absinthe, shown in Figure 4, comes in handy.

Figure 4: Absinthe tool takes the pain out of blind SQL injection testing.
7. Reverse engineering the system
The reverse engineering trick looks for software exploits, memory corruption weaknesses and so on. In Exploiting Software: How to Break Code by Greg Hoglund and Gary McGraw, you'll find a discussion about reverse engineering ploys.
8. Google hacks
Google hacks use the extraordinary power of the Google search engine to ferret out SQL Server errors -- such as "Incorrect syntax near" -- leaking from publicly accessible systems. Several Google queries are available at Johnny Long's Google Hacking Database. (Look in the sections titled Error Messages and Files containing passwords.) Hackers use Google to find passwords, vulnerabilities in Web servers, underlying operating systems, publicly available procedures and more that they can use to further compromise a SQL Server system. Combining these queries with Web site names via Google's 'site:' operator often turns up juicy info you never imagined you could unearth.
9. Perusing Web site source code
The source code can also turn up information that may lead to a SQL Server break in. Specifically, developers may store SQL Server authentication information in ASP scripts to simplify the authentication process. A manual assessment or Google could uncover this information in a split second.
10. Cracking SA passwords
You can use the following ways to secure the SA login.
a.           Use Windows Authentication Mode
One way of doing this is to use "Window Authentication mode" from the Server Properties dialog which will allow access to only Windows logins and not SQL logins. Microsoft also recommends Windows authentication mode only. One thing to note is that even though you are using Windows authentication, you must use a strong password for the SA account, because anyone can change the authentication mode by updating the registry value and restarting the SQL Services.
Replace the username and password with Integrated Security=SSPI;
So the connection string should be
<connectionStrings>
<add name="NorthwindContex"
   connectionString="data source=localhost;   initial catalog=northwind;persist security info=True;
   Integrated Security=SSPI;"
providerName="System.Data.SqlClient" />
</connectionStrings>

To change this setting in SQL Server Management Studio, right-click the server, click Properties and go to Security page as shown below.
b.           Disable the SA Login
Disabling the SA account is a good option to prevent its use. When it is disabled no one can use it in any circumstance until it is enabled. The only disadvantage is that we can't use the SA account in an emergency.
You can use the below T-SQL to disable SA account.
--Query to disable the SA account.
ALTER LOGIN sa DISABLE;
This query will check the status of the SA account. A value of 1 indicates the account is disabled and 0 indicates the account is enabled.
--Query to check account status
SELECT name,is_disabled from sys.server_principals where name='sa'

c.           Rename the SA Login
You can also rename the SA account which will prevent hackers/users to some extent. The one disadvantage is that it does not change the SID for the SA account which by default is 0x01, so someone could find the new name by looking up the SID.
--Query to check account status
ALTER LOGIN sa WITH NAME = [mssqltip];
This query could be used to lookup the name for the SA account based on the SID.
SELECT * FROM sys.syslogins WHERE sid = 0x01

11. Make the system administrator account's password complicated :
If you are using mixed-mode authentication in SQL Server, always set a complex password for the SA account. As a best practice, always avoid using the SA account to connect Web applications to SQL Server. One should always avoid using the system administrator account to perform day-to-day maintenance activities. For day-to-day stuff, use Windows accounts with appropriate permissions. As a best practice, don't forget to change SA account passwords once every few days.
12. Audit logins
As a SQL Server security best practice, you should always audit failed logins to SQL Server. Once you have enabled login auditing in SQL Server, the failed and successful login information will be written to the SQL Server error logs, which can be monitored regularly to look into suspicious activities from time to time.
13. Turn off the SQL Server browser service
As another SQL Server security best practice, a database admin should turn off the SQL Server browser service when running a default instance of SQL Server. Even if you are running a named instance, you can explicitly define the port and mention the port within the application connection strings to connect to a named instance of SQL Server.
14. Disable unused features in SQL Server
Disable features such as XP_CMDSHELL, OLE AUTOMATION, OPENROWSET and OPENDATASETwhen not in use to reduce surface area attacks. In SQL Server 2005 you can enable or disable these features using the SQL Server Configuration Manager. If you are using SQL Server 2008 or higher versions you can enable or disable the above-mentioned features using the policy-based management feature.
15. Decrease privileges for the SQL Server service account
A final best practice is that a database administrator should always run SQL Server services in a minimally privileged local domain account. Avoid running SQL Server services under the contexts of the local system, local administrator or domain administrator accounts. However, make sure that the SQL Server service account has "full control" permissions on data, log and backup directories for read and write activities.


How to Get SQL Server Security Horribly Wrong or Failures


It is no good doing some or most of the aspects of SQL Server security right. You have to get them all right, because any effective penetration of your security is likely to spell disaster. If you fail in any of the ways that Robert Sheldon lists and describes, then you can't assume that your data is secure, and things are likely to go horribly wrong.

Failure 1: Not securing the physical environment
A database team might go through extraordinary measures to protect its SQL Server instances and the data they contain, but not necessarily extend that diligence to the physical environment, often because the assets themselves are not in the team’s control. Yet physical devices-such as servers, backup drives, or disks in a storage area network (SAN)-can be compromised just like any software or network component, so whoever is in charge of those devices better take heed.
All it takes is one disgruntled employee with the access to the physical assets to turn an organization upside down. Before anyone knows what happens, the individual can walk off with a backup drive full of credit card numbers, health records, company secrets, or other sensitive information, resulting in permanent damage to the organization’s reputation and financial well-being. Even if the data is encrypted, an employee with the necessary privileges, access to the security certificates, and a bit of know-how can make full use of that data on the open market.
Organizations serious about data security must implement defense in depth strategies that protect all layers of the data infrastructure, including the physical components, whether from inside threats or those coming from outside the organization. Every server and disk that hosts a SQL Server instance or data file must be physically protected so that only those who absolutely need access to those devices can get at them. That includes devices used for replications, mirrored databases, backups, or log files. The number of workers who have access to the physical devices should be strictly limited, with security measures scrutinized and updated regularly.
Another challenge, perhaps even more difficult to address, is trying to “enlighten” those wayward employees with privileged access who are careless with their desktops and laptops, leaving them unattended and unlocked for varying lengths of time. Anyone with less than honorable intentions can stroll along and change or copy sensitive data with a little bit of insight into the systems, not only making the careless user look bad, but again putting the company’s reputation and financial happiness on the line. If the imprudent user is a creature of habit, the miscreant thief has an even easier time getting at the sensitive data. Given the possibility of such a scenario, the only hope for the organization is employee education and IT policies that try to mitigate such risks as much as possible.

Failure 2: Not protecting the server environments
SQL Server can spread across environments far and wide, its impact reaching into machines and folders on the other side of the known network. Installation files might be located on a nearby server, log files on another device, data files on the new SAN down the hall, and backups in a remote data center. Even within the host, SQL Server also makes use of operating system (OS) files and the registry, spreading its impact even further.
Anything SQL Server touches should be protected to minimize the potential for a SQL Server instance or its data being compromised. For example, a database might contain sensitive data such as patient health records. Some of that data can end up in the log files at any given time, depending on the current operations. Even if all other files are carefully protected, a less-then-secure log file can have far reaching implications if the network or attached devices are compromised.
Database and IT teams should take every precaution to protect the files and folders that support SQL Server operations, including OS files, SQL Server installation files, certificate stores, data and log files-everything. Administrators should not assign elevated privileges to any related folder and should not share any of those folders on the network. Access to the files and folders should be limited to the greatest degree possible, granting access only to those accounts that need it. Teams should always keep in mind the principles of least privilege when it comes to NTFS permissions, that is, granting access only to the degree absolutely necessary and nothing more. The same goes for the machine as a whole. Don’t assign administrative access to a server when a user requires only limited privileges.

Failure 3: Implementing inadequate network security
When you consider all the ways a network can be hacked, it’s remarkable we can do anything safely. Weak network security can lead to viruses and network attacks and compromised data of unimaginable magnitude. Cyber-criminals able to exploit network flaws are not only able to inflict damage, but also download boatloads of data before being detected, whether credit card information, social security numbers, company strategies, or a host of other types of sensitive data.
An organization must do everything possible to protect its network and the way SQL Server connects with that network. One place to start is to ensure that you’re running the necessary antivirus software on all your systems and that the virus definitions are always up to date. Also be sure that firewalls are enabled on the servers and that the software is current. Firewalls provide network protection at the OS level and help to enforce an organization’s security policies.
In addition, configure SQL Server to use TCP/IP ports other than default one (such as 1433 or 1434). The default ports are well known and are common targets for hackers, viruses, and Trojan horses. In addition, configure each instance to use a static port, rather than allowing the number to be assigned dynamically. You might not be able to ward off all attacks, but this way you’re at least obscuring the pathways.
To further obscure your SQL Server instance, consider disabling the Browser service to prevent snoopers from being able to search for it on the network. Clients can still access the instance as long as their connections specify the correct port number or named pipe. If disabling the Browser service is not an option, at least hide any instances you don’t want easily discovered, once again forcing any client connections to have to provide the port number or named pipe.
In addition, you should disable the network protocols that are not needed. For example, it’s unlikely you need to have both the Named Pipes and TCP/IP protocols enabled. In addition, you should grant the CONNECT permission only the endpoints or logins that require it; for all others, explicitly deny the permission. If possible, avoid exposing the server that’s running SQL Server to the Internet altogether.

Failure 4: Not updating and patching your systems
Some of you might recall what happened in 2003. The SQL Slammer computer worm infected over 75,000 servers running SQL Server. Within 10 minutes of deployment, the worm infected more than 90% of vulnerable computers and took down thousands of databases. Slammer exploited a buffer-overflow vulnerability in SQL Server to carry out an unprecedented denial-of-service (DoS) attack. The rest is cyber history.
The interesting part of this story is that the bug had actually been discovered long before the attack. In fact, Microsoft provided a fix six months in advance of the DoS onslaught. The organizations that got hit by Slammer had failed to patch their SQL Server instances.
Because of the onslaught of threats aimed at SQL Server and other systems, companies like Microsoft are regularly releasing service packs and patches and security fixes and other types of updates to address any new found vulnerabilities. What these vendors can’t do, however, is force their customers to apply the updates.
In all fairness, there’s good reason why many DBAs and other administrator types can be slow to getting around to patching their systems. Applying these fixes can be a big deal. They take time and testing and careful consideration. If you apply an update to a production server without first testing it you risk taking down the entire system. Overworked DBAs must have the time in their schedules and the resources necessary to apply these updates right.
Yet such incidents as the Slammer debacle demonstrate how critical it is to keep your SQL Server instances up to date. Not only can such attacks lead to excessive resource consumption, server downtime, and corrupt data, but also to even more serious concerns. The information gathered from a successful DoS attack can be used to launch subsequent attacks, some of which might be aimed at getting at the actual data.
Microsoft is constantly releasing critical updates and fixes for both Windows and SQL Server. You can manually download and install them, or set up your systems to update the software automatically, if you can afford to take such risks. More often than not, you’ll want to first test the update and schedule a time to implement it into a production environment. It is no small matter, certainly, but keeping Windows and SQL Server current is one of the most effective strategies you can employ for protecting your system’s data.

Failure 5: Maintaining a large surface attack area
We’re not talking rocket science here, just common sense. The more SQL Server features and services you have enabled, the larger the surface attack area and the more vulnerable your system is to potential attacks. When setting up SQL Server, you should install only those services you need and disable those features you don’t plan to use anytime soon. Only the services and features for which there is a credible business need should be running.
Cyber criminals are a diligent lot, and the more you give them to work with, the happier they are. Given the proliferation of zero-day exploits, the less you can leave yourself exposed, the better. Why install Analysis Services or Integration Services on a production server when all you need is the database engine?
Some features in particularly can raise the stakes on your system’s vulnerability. For example, xp_cmdshell let’s you execute a command against the Windows host. If that feature is enabled on a SQL Server instance and a hacker gains access, xp_cmdshell can open doors to the OS itself. Other features too should be enabled only if needed, such as the Active Directory Helper service or VSS Writer service. You even have to be cautious with OPENROWSET and OPENDATASOURCE because they can facilitate access to external systems. In addition, you should consider disabling mail-related features that are not needed, such as the Database Mail service or the sp_send_mail system stored procedure. And don’t install the sample databases or code on your production servers.
If you’re running SQL Server 2005, you can use the Surface Area Configuration (SAC) tool to enable or disable installed components. For whatever components SAC doesn’t address, you can use Configuration Manager, the sp_configure system stored procedure, or a tool such as Windows Management Instrumentation.
Starting with SQL Server 2008, the Policy-Based Management system replaces SAC, providing one location to address all your surface configuration needs, in addition to configuring other components. For example, the following dialog box shows a new policy named Surface area conditions, with the Remote queries disabled condition selected. The condition specifies that the OPENROWSETand OPENDATASOURCE functions should be disabled.
Whichever tools you use, the important point is to install and enable only those components you need. You can always add or enable other features later. Just don’t give hackers any more ammunition than they already have.

Failure 6: Using improper authentication
SQL Server supports two authentication modes: Windows Authentication and Mixed Mode. Windows Authentication, the default authentication type, leverages Windows local accounts and Active Directory network accounts to facilitate access to the SQL Server instance and its databases. Mixed mode authentication supports both Windows accounts and SQL Server accounts (SQL logins). You can specify the authentication mode when installing SQL Server or change it after SQL Server has been installed by updating the server properties, as shown in the following figure.
Microsoft (and just about everyone else) recommends that you use Windows Authentication whenever possible, falling back on Mixed mode only to support backward compatibility, access to SQL Server instances outside the domain, and legacy applications. Windows Authentication is more secure because it can take advantage of the Windows and Active Directory mechanisms in place to protect user credentials, such as the Kerberos protocol or Windows NT LAN Manager (NTLM). Windows Authentication also uses encrypted messages to authorize access to SQL Server, rather than passing passwords across the network. In addition, because Windows accounts are trusted, SQL Server can take advantage of Active Directory’s group and user account administrative and password management capabilities.
SQL Server accounts and passwords are stored and managed within SQL Server, with passwords being passed across the network to facilitate authentication. Not only does this make the authentication process less secure, but it also means you can’t take advantage of the password controls available to Active Directory. In addition, you must be diligent about managing the passwords within SQL Server, such as mandating strong passwords and expiration dates as well as requiring new logins to change their passwords. You must also guard against accounts being created with blank passwords, including accounts created by third-party products. Weak passwords leave your database open to brute force attacks and other nefarious activities. Blank passwords open up your databases to everyone and everything.
Also with Mixed Mode authentication, you have to be particularly wary of that powerful built-in SA account. At one time, this had actually been created with a blank password. Fortunately, that has not been the case since SQL Server 2005. Many database pros recommend that you rename the SA account. Others suggest you disable it and never use it. Still others would have you do all three, not a bad strategy, given how well known the account is and its history of abuse. At the very least, you should assign a complex password to the account and keep it disabled.
It should also be noted the SQL Server creates the SA account even if Windows Authentication is the selected mode, assigning it a randomly generated password while disabling the account. You might consider applying a complex password in this case as well, and certainly don’t enable the account.

Failure 7: Assigning the wrong service accounts
Each SQL Server service requires a Windows account to run so the service can access resources such as networks and file folders. Because orchestrating this access can be somewhat cumbersome, services are often assigned high-privileged built-in accounts such as Local System or Network Service, thus preventing those mysterious SQL Server error messages that can pop up at peculiar moments, providing information that is so obscure that even the Google gods cannot offer an answer.
The problem with using these accounts is that, if SQL Server were to be compromised, the OS too could be compromised-to the degree that the service account has access. These built-in accounts can actually inherit elevated privileges in Active Directory, which are not required in SQL Server. For example, you can easily assign the Local System account to a SQL Server service, as shown in the following figure.
The Local System account is a very high-privileged account with extensive access to the local system. It is like granting a user administrative privileges to the server. If SQL Server were to be compromised, the would-be hacker could potentially have unrestricted access to the machine and its resources.
Despite the convenience that these built-in accounts bring, you should be following the principles of least privilege when assigning accounts to SQL Server services. The accounts should have exactly the permissions necessary for the service to do its job. You should also use a different account for each service, with the permissions set up specifically to meet the needs of that account.
For example, SQL Server Agent will likely require a different set of permissions from Integration Services. Also, avoid assigning accounts being used by other services on the same server. In addition, you must take into consideration whether the service will need access to domain resources, such as a data file on a network share. Keep in mind too that service accounts should also be governed by good password management policies, such as enforcing complex passwords and expiration dates.

Failure 8: Failing to control access to SQL Server resources
Like all aspects of SQL Server access, account access should adhere to the principles of least privilege, assigning only the permissions necessary to perform specific functions. Along with this principle, we can add another-separation of duties. This ensures against conflicts of interest and the inadvertent combination of privileges that lead to excessive access.
All too often, database administrators (or someone) will assign one or two sets of generic privileges to all users, without thought to the consequences of such action. For example, an assistant bookkeeper might need access to sales totals and quantities, but is granted access to the entire database, which includes sensitive customer information. That employee could abuse that position by modifying records or stealing data. Together, the principles of least privilege and separation of duties help to ensure that database owners maintain control over who can access SQL Server and its data and what levels of access they have.
This also goes for the applications and services accessing the database. They too should be using low-privileged accounts that grant only the access needed. In addition, be sure to remove unused default accounts created by third-party apps (as well as any other accounts not in use).
When possible, avoid granting individual accounts access to SQL Server and instead grant access to the security groups that contain these accounts, granting them the specific access they need. This helps to think in terms of separation of duties and makes it easier to manage large numbers of accounts. Also consider disabling the guest accounts in your databases so that members of the public server role can’t access those databases, unless they’ve been specifically granted the permissions.
Pay particular attention to how you’re assigning administrate permissions to users. For example, choose widely which accounts are added to the sysadmin fixed server role. Group members can do just about anything they want in SQL Server. And don’t use the SA admin account to manage SQL Server, as mentioned earlier.
You should also avoid assigning the CONTROL SERVER permission to individual accounts. It provides full administrative privileges over SQL Server and is already granted to the sysadmin group. Many database pros also recommend that you remove the Windows BUILTIN/Administrators group from the sysadmin role if it has been added. (It was added by default prior to SQL Server 2008.) Although removing the group lets you better control SQL Server access, you have to be careful doing so because it can impact your SQL Server installations.
Make use of SQL Server’s hierarchical permission structure in which the principals (users and roles) can be granted or denied to the securables (the databases and its objects). For example, you can grant a principal access at the database, schema, or table level. In the following figure, the database role has been granted SELECT and INSERT privileges to the ZipCodes table, but denied UPDATE and DELETE privileges.
By taking this approach, you can ensure that users perform only specific tasks and not perform others. Again, the key is to follow the principles of least privilege and the separation of duties. This applies to administrative accounts as well as other types of accounts.

Failure 9: Failing to encrypt sensitive data
An organization less diligent about security might assume that, because SQL Server is a backend system, the databases are inherently more secure than public-facing components and be satisfied that the data is ensconced in a protective layer. But SQL Server still relies on network access and is consequently exposed enough to warrant protection at all levels. Add to this the possibility that physical components such as backup drives can be lost or stolen (most likely the latter), and you can’t help but realize that no protection should be overlooked.
Encryption is one such protection. Although it cannot prevent malicious attempts to access or intercept data, no more than it can prevent a drive from being stolen, it offers another safeguard for protecting your data, especially that super-sensitive stuff such as credit card information and social security numbers. That way, if the data has been accessed for less than ethical reasons, it is at least protected from prying eyes.
SQL Server supports the ability to encrypt data at rest and in motion. On the at-rest side, you have two options: cell-level encryption and Transparent Data Encryption (TDE). Cell-level has been around for a while and lets you encrypt individual columns. SQL Server encrypts the data before storing it and can retain the encrypted state in memory.
Introduced in SQL Server 2008, TDE encrypts the entire database, including the log files. The data is encrypted when it is written to disk and decrypted when being read from disk. The entire process is transparent to the clients and requires no special coding. TDE is generally recommended for its performance benefits and ease of implementation. If you need a more granular approach or are working with SQL Server 2005 or earlier, then go with cell-level encryption.
SQL Server can also use the Secure Sockets Layer (SSL) protocol to encrypt data transmitted over the network whether between SQL Server instances or between SQL Server and a client application. In this way, data can be protected throughout a session, making it possible to pass sensitive information over a network. Of course, SSL doesn’t protect data at rest, but when combined with TDE or cell-level encryption, data can be protected at every stage.
An important component of any encryption strategy is key management. Without going into all the gritty details of SQL Server key hierarchies, master keys, and symmetric and asymmetric keys, let’s just say you need to ensure these keys (or certificates) are fully protected. One strategy is to use symmetric keys to encrypt data and asymmetric keys to protect the symmetric keys. You should also password-protect keys, and always back up the master keys and certificates. Also back up your database to maintain copies of your symmetric and asymmetric keys, and be sure those backups are secure.

Failure 10: Following careless coding practices
After all these years, after all the press, after all the countless articles and recommendations and best practices, SQL injection still remains a critical issue for SQL Server installations. Whether because of sloppy coding within SQL Server or within the web application passing in SQL, the problem persists. Hackers are able to insert malicious code into a string value passed to the database and in the process do all sorts of damage-deleting rows, corrupting data, retrieving sensitive information. Any code that accesses SQL Server data should be vetted for potential SQL injection and fixed before going into production. Fortunately, those fixes are often quite straightforward, such as validating user input or delimiting identifiers.
But SQL injection is not the only risk. Issues can arise if the execution context within procedures, functions, or triggers is not explicitly called out. By default, the code executes as the caller, but this can lead to problems if an account with elevated privileges has been compromised. However, as of SQL Server 2005, you have more control over the execution context (using EXECUTE AS) and can specify the account to use as the execution context, thus ensuring full control over a procedure’s capabilitiy.
Another way coders can protect their databases is to create procedures, functions, and views to present the data without providing access to the base tables. This helps to abstract the actual schema and allows access to be controlled at the abstraction layer, restricting access altogether to the tables themselves. This approach also has the benefit of more easily accommodating changes to the application as well as to the underlying data structure. In some cases, the database team won’t have this option because of application requirements or the technologies being used, such as a data abstraction layer, but when it is possible, providing this extra layer of protection can be well worth the effort.
There are, of course, plenty of other coding practices that can result in compromised data. The key is to make certain that all code is reviewed and tested for vulnerabilities, before it is implemented in production.

Failure 11: Not verifying SQL Server implementations
Regardless of the safeguards you’ve implemented to protect your SQL Server databases and their data, security can be breached without you being aware that something is wrong. The only way you can fully protect your data is to monitor and verify your SQL Server installations. Monitoring in this sense does not refer to auditing (which we’ll discuss shortly), but to the process of assuring that everything is running as it should, the data is intact, and nothing too strange is going on.
For example, you should be monitoring CPU, memory, and disk usage, not only for performance considerations, but also to ensure you’re not seeing any anomalies that might point to such issues as illegal data downloads or malware accessing or modifying the databases and their data. Activity monitoring can also be a useful stopgap measure until you get a chance to apply the latest security patches. You should take whatever steps might help you expose abuses that would otherwise go unnoticed-until it’s too late.
You can also make use of such tools as DBCC CHECKDB, which can help uncover data corruption as a result of a cyber-attack or illegal access. Spot-checking security-related configuration settings can also be useful in discovering if a rogue user has gained access and is wreaking havoc on the permissions or other settings. In general, you want to check your databases to make sure they’re doing only what they’re supposed to be doing and that data is in the state you expect it to be.
You can also make use of such tools as SQL Server Best Practices Analyzer. The Analyzer is free and can help you gather information about security settings and identify vulnerabilities. The tool uses SQL Server recommendations and best practices to uncover potential security risks. And don’t forget the server hosting SQL Server. For example, you (or an IT administrator) can use Microsoft Security Compliance Manager to enhance the server’s security via Group Policy.

Failure 12: Failing to audit your SQL Server instances
Auditing is a big topic, too big to cover in just a few paragraphs, but it is a topic too important not to mention. One of the most beneficial steps you can take as part of a complete security strategy is to implement auditing. If you’re not monitoring user activity, you might be opening yourself up to severe consequences in the form of corrupt or compromised data.
You have several options for auditing your systems. You can use SQL Server’s built-in features (which have greatly improved since SQL Server 2008), or you can implement one of many available third-party solutions, which usually come in the form of host-based agents or network-based monitors. There are pros and cons to any solution, with performance and cost being two of the main considerations. The important point is to maintain an audit trail of all access to sensitive data. Without such an audit trail, you risk being out of compliance as well as having your data compromised.
You will, of course, have to balance the overhead that comes to auditing against the need to protect sensitive information. You’ll have to take this on a case-by-case basis. Determine the level of granularity needed to ensure a credible audit as well as which operations and data should be audited. At the very least, you should audit both successful and failed login attempts. Also keep in mind any compliance-related requirements that might govern your auditing strategy.


SQL Script to list out name of tables and its records in the database

SET NOCOUNT ON  DBCC UPDATEUSAGE(0)  -- DB size. EXEC sp_spaceused -- Table row counts and sizes. CREATE TABLE #temptable  (      [name] NVA...