Security Archives - Business Intelligence Info

Tag Archives: Security

SQL Server Security – Fixed server and database roles

April 12, 2021 BI News and Info

Managing Security for SQL Server is extremely important. As a DBA or security administrator, you need to provide access for logins and database users to resources within SQL Server. SQL Server has both server and database resources where access might need to be granted. Access to these resources can be granted to either individual logins or database users or can be granted to roles, for which logins or database users can be members. Granting access via a role is known as role-based security.

There are two types of roles: fixed or user-defined. In this article, I will discuss the different fixed server and database roles provided with SQL Server and how these roles can be used to support role-based security to simplify providing access to the different SQL Server resources. In a future article, I will discuss user-defined server and database roles.

What is role-based security?

Role-based security is the concept of providing logins and/or database users access to a SQL Server resource by being a member of a role. A role is an object in SQL Server that contains members, much like a Windows group contain members. When a login or database user is a member of a role, they inherit the role’s permissions.

When role-based security is used, the actual access permissions to SQL Server resources are granted to a role and not a specific login or user. Role-based security reduces the amount of administration work needed to grant and manage security when multiple logins or users require the same access to SQL Server resources. Once a role has been set up, and the appropriate permissions are granted to it, it is just a simple matter of adding logins or users to the role to provide them with the same access as the role. Without using roles, an administrator would need to grant the same permissions to each login or database user, thus causing additional administration work. There is also the possibility of making an error which would result in some logins and users getting the wrong set of permissions.

There are two types of fixed roles in SQL Server: Server and Database. The fixed server roles provide specific security access to server resources. In contrast, the fixed database roles provide access to database resources.

Fixed server roles

Fixed Server roles have server-wide scope. They come with a set of canned permissions tied to them. The permissions for server roles can’t be changed or extended.

There are nine different fixed server roles provided with SQL Server, which are shown in Table 1, along with a description. The information in the table is directly from the Microsoft SQL Server documentation.

Table 1: Fixed Server Roles

Server Role	Description
sysadmin	Members of the sysadmin fixed server role can perform any activity in the server.
serveradmin	Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin	Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins. IMPORTANT: The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.
processadmin	Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadmin	Members of the setupadmin fixed server role can add and remove linked servers by using Transact-SQL statements. (sysadmin membership is needed when using Management Studio.)
bulkadmin	Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmin	The diskadmin fixed server role is used for managing disk files.
dbcreator	Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
public	Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public. Note: public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles.

Each fixed server role provides a unique fixed set of permissions that can provide different kinds of access to server resources. The set of permissions associated with fixed server roles (with the exception of the public server role) cannot be modified.

The public role is a little different from all other fixed server roles in that you can grant permissions to this role. When permissions are granted to the public role, all logins with access to SQL Server will inherit the permissions of the public role. The public role is a great way to provide some default permissions to every login.

When a login, Windows account or Windows group becomes a member of one of these server roles, they inherit the rights associated with the role. If someone needs the same rights as a server role, it is much easier to make them a member of the role instead of granting them access to each of the permissions associated with a role. Additionally, when you want to grant the same server rights to multiple logins, placing each login in the same server roles makes it easy to accomplish this, ensuring they get exactly the same permissions. User-defined roles can also be added as members of a server role. I’ll leave the discussion about user-defined roles for another article.

There are a number of different stored procedures, views and functions that can be used to work with fixed server roles. If you are unsure of the permissions associated with a server role, you can use the system stored procedures sp_srvrolepermission to displays the permission assigned to a single fixed server role or all the fixed server roles. The code in Listing 1 shows two different examples of how to use this stored procedure.

Listing 1: Using sp_srvrolepermission

– Displaying all server role permissions

EXEC sp_srvrolepermission;

– Displaying permissions for only the dbcreator role

EXEC sp_srvrolepermission @srvrolename = ‘dbcreator’;

For a complete list of all stored procedures, commands, views and functions that work with fixed server roles, you can check out the list by using this link.

Adding a login to a fixed server role can be done using SQL Server Management Studio (SSMS) or TSQL. To use SSMS, follow these steps:

Connect to an instance
Expand the Security item
Expand the Server Roles item
Right-click on the fixed server role and then click on the properties option
Click on the Add button on Server Roles Properties page
Select the login or user-defined server role that you want to add to fixed server role
Click on a series of Ok buttons to complete the addition of new member to the role

Before clicking to add the member, the dialog should look like Figure 1.

word image SQL Server Security – Fixed server and database roles

Figure 1: Add a new login to a fixed server role.

Alternatively, you can use the TSQL code to add a login to a fixed server role. The code in Listing 2 adds the Red-Gate login to the sysadmin fixed server role.

Listing 2: Adding a login to the sysadmin fixed server role.

ALTER SERVER ROLE sysadmin ADD MEMBER [Red-Gate];

Removing logins from a fixed server role can be done as easily as you added them by using SSMS or TSQL. The code in Listing 3 shows how to remove the Red-Gate login from the sysadmin fixed server role.

Listing 3: Removing login from sysadmin fixed server role.

ALTER SERVER ROLE sysadmin DROP MEMBER [Red-Gate];

Fixed server roles are a great way to provide DBAs, Security Admins, and operators access to the server resources they need to perform their job duties. By using server roles, you can simplify the granting of permissions to server resources. In addition to fixed server roles, there are also fixed database roles.

Database Roles

To help manage security at the database level, SQL Server has databases roles. Just like server roles, there are two different types of database roles: fixed and user-defined. Fixed database roles are just like fixed server roles, meaning they have a specific set of permissions associated with each one that cannot be altered. Fixed database roles only provide permissions to database resources in a specific database.

If a database user is a member of a fixed database role, they inherit the permissions that have been pre-defined for the fixed database role. Each database contains the same set of fixed database roles. Table 2 contains the names and definitions for each fixed database role definition, as found in the Microsoft documentation.

Table 2: Fixed Database Roles

Fixed Database Role Name	Description
db_owner	Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server. (In SQL Database and Azure Synapse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)
db_securityadmin	Members of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges and their actions should be monitored.
db_accessadmin	Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator	Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin	Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter	Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader	Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter	Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader	Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

A few additional special fixed database roles only apply to the msdb database or SQL Database on Azure. For more information on these special roles, see Microsoft documentation here.

There is also the public database role. Just like the public server role, rights can be granted to the public database role. When rights have been granted to the public database role in a database, those rights are inherited by each database user that has been defined as a user of the database. The public role is a great way to provide the same permissions to database resources for every database user in a database.

Not all organization will use each of these roles to provide access. Most shops use the db_datareader, and db_datawriter roles. If you make a database user a member of these roles, they will be able to read and/or update any user table in the database. Not only that, but they will also be able to update and/or read any data from any new user table that might be added in the future. This could be a good thing or a bad thing. It is a good thing if you want users to automatically gain read and/or update rights to all new user tables, regardless of the table. If you ever think you might want to add one or more tables to your database that only a few database users should have access to, then avoid using these two roles to provide blanket read and/or write access to database tables. Because these two roles automatically provide access to any new user table defined in the future, some shops prohibit the use of these roles to ensure database users only have access to the tables they need to perform their job function.

Just like fixed server roles, there are a number of system stored procedures, commands, views, and functions that can be used to display and manage fixed database roles. The code in Listing 4 shows how to use one of those system stored procedures sp_dbfixedrolepermission, to display all the permissions associated with each fixed database role in the AdventureWorks2019 database, as well as how to use this stored procedure to display just the permissions associated with the single fixed database role db_datareader.

Listing 4: Displaying permissions associated with fixed database roles

USE AdventureWorks2019;

– Show all permissions for fix database roles

EXEC sp_dbfixedrolepermission;

–Show permissions for db_datareader

EXEC sp_dbfixedrolepermission @rolename = ‘db_datareader’;

For a complete list of all stored procedures, commands, views and functions used to display and manage fixed database roles you can review the documentation found here.

In order for a database user to inherit the permissions of a fixed database role, they need to be a member of a fixed database role. To determine if a databases user is a member of a database role, you can review the role membership using SSMS by following the steps:

Connect to an instance
Expand Databases
Expand the database for which you want to review fixed database roles
Expand the Security item
Expand the Roles item
Expand the Database Roles Item
Double click on the role in which you want to see members
Review the properties window display to see the members of the role

Figure 2 shows the members of the db_datareader role:

word image 1 SQL Server Security – Fixed server and database roles

Figure 2: Review fixed database role permissions

An application might need to programmatically determine if a database user is a member of roles. The IS_MEMBER function allows you to write TSQL code to do that. Using this function would allow you to build an application that displays different menu options for different database users, depending on the database roles that the current user is a member. To programmatically determine if the currently logged-on database user is a member of db_datawriter role, in the AdventureWorks2019 database, you could use the code in Listing 5.

Listing 5: Determining if current database user is a member of a role

USE AdventureWorks2019;

SELECT IS_MEMBER(‘db_datawriter’);

The IS_MEMBER function returns a 1 if the current user is a member of the db_datawriter role or 0 if the user is not a member of this role.

Predefined Server or Database Roles

Several predefined server and database roles are provided in SQL Server. These predefined roles provide members with a fixed set of permissions based on the role. Using these predefined roles makes it easy to grant logins or database users access to a predefined set of permissions by just making them a member of a server or database role. One thing to keep in mind when using fixed server and database roles is that the permissions cannot be changed or expanded. Using predefined server and databases roles are a great way to provide a set of canned access to server and/or database resources with minimal administrative effort.

Let’s block ads! (Why?)

SQL – Simple Talk

The Easier Way For Banks To Handle Data Security While Working Remotely

April 10, 2021 CRM News and Info

When you think of an industry that could “never” allow working from home, banking would be at the top of the list. There are too many security issues involved.

Banking systems have social security numbers, account balances, mortgage details. Everything you wouldn’t want to fall into the wrong hands.

Last year very few banks had planned for a complete shutdown, yet all of a sudden, to continue serving customers, they had to quickly find a way for employees to work remotely.

Most banks did it the hard way, which is also the expensive way. Some banks found a better way.

The hard way:

Most banks use legacy technology that runs on internal servers. To access it remotely, they must use a Citrix encrypted access link. Each of these connections requires a license and costs money.

In the past, perhaps just a few salespeople or top executives were given one of the RSA access token key fobs to go through an encrypted Citrix connection and securely log into the bank systems.

In 2020, to remain fully operational, suddenly, banks were faced with the expense of possibly hundreds of extra remote licenses. More likely, only a few selected people received a license, and the others were rotated into the office on a schedule. Except when a branch had someone exposed to COVID, and then the entire team had to quarantine.

Of course, this impacts productivity and puts a strain on the workers in the office. Another casualty is that essential systems, such as the CRM system, may not be kept up to date.

It is hard enough to get bank employees to keep a CRM system up to date. If they do not have access from home, they have to wait until they are rotated back into the office, which in some cases is two weeks. Then if the employee is only back in the branch for a short time, likely their first priority is not to update the CRM system. Admins who have remote access have to think of workarounds such as manually exporting lists, asking people for updates, and then entering the updates for them.

This “system”, both from a technical and operational perspective, is expensive and inefficient.

The easy way:

Microsoft’s mission is to empower banks to achieve more by delivering solutions with security, privacy, compliance, and transparency in mind. Microsoft Dynamics 365 was built for the cloud and designed specifically for remote access. Everyone licensed in the system can use it, no matter where they are, and stay secure. Microsoft Dynamics 365 with Crowe CRM for Banking is a system built for the banking industry. There is no extra cost for remote connections. There is no extra cost for the world-class governance you are afforded in the Microsoft cloud. Microsoft Dynamics 365 helps bank employees remain productive without increasing cybersecurity risk.

Employees can stay in touch with coworkers using chat applications like Microsoft Teams and shared documents using SharePoint or OneDrive. They can replace in-person meetings with conference calls. All this is done on the Microsoft platform.

Microsoft’s security approach begins with Zero Trust, which is based on the principle: never trust, always verify. Every access request is strongly authenticated and inspected before access is granted. This approach helps Microsoft protect employees, devices, applications, data – no matter where users are located.

Next steps:

While we hope that business will soon be getting back to normal after the pandemic, will your bank be ready if something similar happens again? Even if we do not have another full lockdown, would your team be more productive if it was easy for them to access systems, securely, from any location where they might be working?

Crowe works with more than 2,000 financial services organizations across the country, including about two-thirds of the top 100 U.S. banks. The Crowe’s Banking Consulting team is dedicated to helping banks improve efficiency and achieve greater success.

Crowe professionals focus on helping banks manage technologies, enhance systems, address complex data and analysis challenges, and organize processes for optimal efficiency. We provide practical advice and proven methods designed to help organizations create lasting change.

Visit CroweCRM.com for more information about Crowe CRM solutions and services for Microsoft Dynamics 365 software.

By Ryan Plourde, Crowe, a Microsoft Dynamics 365 Gold Partner www.CroweCRM.com

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

IBM bets homomorphic encryption is ready to deliver stronger data security for early adopters

April 3, 2021 Big Data

Join Transform 2021 for the most important themes in enterprise AI & Data. Learn more.

The topics of security and data have become almost inseparable as enterprises move more workloads to the cloud. But unlocking new uses for that data, particularly driving richer AI and machine learning, will require next-generation security.

To that end, companies have been developing confidential computing to allow data to remain encrypted while it is being processed. But as a complement to that, a security process known as fully homomorphic encryption is now on the verge of making its way out of the labs after a long gestation period and into the hands of early adopters.

Researchers like homomorphic encryption because it provides a certain type of security that can follow the data throughout its journey across systems. In contrast, confidential computing tends to be more reliant upon special hardware which can be both powerful but also limiting in some respects.

Companies such as Microsoft and Intel have been big proponents of homomorphic encryption. Last December, IBM made a splash when it released its first homomorphic encryption services. That package included education material, support, and prototyping environments for companies that want to experiment.

In a recent media presentation on the future of cryptography, IBM director of strategy and emerging technology Eric Maass explained why the company is so bullish on “fully homomorphic encryption” or FHE.

“FHE is a unique form of encryption and it’s going to allow us to compute upon data that’s still in an encrypted state,” Maass said.

Evolving encryption

First, some context. There are 3 general categories of encryption. The two classic ones are encryption for when data is at rest and is stored and then “data in transit” that protects the confidentiality of data as it’s being transmitted over a network.

The third one is the piece that has been missing: The ability to compute on that data while it’s still encrypted.

That last one is key to unlocking all sorts of new use cases. That’s because until now, for someone to process that data, it would have to be unencrypted, which creates a window of vulnerability. That makes companies reluctant to share highly sensitive data involving finance or health.

“With FHE, the ability to actually keep the data encrypted and never exposing it during the computation process, this has been somewhat akin to a missing leg in a three-legged crypto stool,” Maass said. “We’ve had the ability to encrypt the data at rest and in transit, but we have not historically had the ability to keep the data encrypted while it’s being utilized.”

With FHE, the data can remain encrypted when being used by an application. Imagine, for instance, a navigation app on a phone that can give directions without actually being able to see any personal information or location.

Companies are potentially interested in FHE because they could then apply AI to data such as finance and health while being able to promise users that the company has no way to actually view or access the underlying data.

While the concept of homomorphic encryption has been of interest for decades, the problem is that FHE has taken a huge amount of compute power, so much so that it has been too expensive to be practicable.

But in recent years, researchers have made big advances.

For instance, Maass noted that in 2011, it took 30 minutes to process a single bit using FHE. By 2015, researchers could compare two entire human genomes using FHE in less than an hour.

“IBM has been working on FHE for more than a decade and we’re finally reaching an apex where we believe this is ready for clients to begin adopting in a more widespread manner,” Maass said. “And that becomes the next challenge: widespread adoption. There are currently very few organizations here that have the skills and expertise to use FHE.”

FHE ready for its closeup

During the presentation, AI security group manager Omri Soceanu ran an FHE simulation involving health data bring transferred to a hospital. In this scenario, an AI algorithm was being used to analyze DNA for genetic issues that may reveal risks for prior medical conditions.

Typically, that patient data would have to be decrypted first, which could raise both regulatory and privacy issues. But with FHE, it remains encrypted, thus avoiding those issues. In this case, the data is sent encrypted, remains so while being analyzed, and the resulting results are returned also in an encrypted state.

What’s also important to note is that this system was put in place using just a dozen lines of code, a big reduction from the hundreds of lines of code that until recently have been required. By reducing that complexity, IBM wants to make FHE more accessible to teams that don’t necessarily have cryptography expertise.

Finally, Soceanu explained that the simulation was completed in .069 seconds. Just 5 years ago, he said, the same simulation took a few hours.

“Working on FHE, we wanted to allow our customers to take advantage of all the benefits of working in the cloud while adhering to different privacy regulations and concerns,” he said. “What only a few years ago was only theoretically possible is becoming a reality. Our goal is to make this transition as seamless as possible, improving performance and allowing data scientists and developers, without any crypto skills, a frictionless move to analytics over encrypted data.”

Next steps

To accelerate that development, IBM Research has released open-source toolkits while IBM Security launched its first commercial FHE service in December.

“This is aimed at helping our clients start to begin to prototype and experiment with fully homomorphic encryption with two primary goals,” Maass said. “First, getting our clients educated on how to build FHE enabled applications, and then giving them the tools and hosting environments in order to run those types of applications.”

Maass said IBM envisions FHE in the near term being attractive to highly regulated industries such as financial services and healthcare.

“They have both the need to unlock the value of that data, but also face extreme pressures to secure and preserve the privacy of the data that they’re computing upon,” he said.

But he expects over time that a wider range of businesses will benefit from FHE. Many sectors want to improve their use of data which is becoming a competitive differentiator. That includes using FHE to help drive new forms of collaboration and monetization. As that happens, IBM hopes these new security models will drive wider enterprise adoption of hybrid cloud platforms.

The company sees a day, for instance, when due diligence for mergers and acquisitions are done online without violating the privacy of shareholders or when airlines, hotels, and restaurants could use FHE to offer packages and promotions without giving their partners access to details of closely held customer datasets.

Maass said: “FHE will allow us to secure that type of collaboration, extracting the value of the data, while still preserving the privacy of it.”

VentureBeat

VentureBeat’s mission is to be a digital town square for technical decision-makers to gain knowledge about transformative technology and transact.

Our site delivers essential information on data technologies and strategies to guide you as you lead your organizations. We invite you to become a member of our community, to access:

up-to-date information on the subjects of interest to you
our newsletters
gated thought-leader content and discounted access to our prized events, such as Transform 2021: Learn More
networking features, and more

Become a member

Let’s block ads! (Why?)

Big Data – VentureBeat

What to monitor for SQL Server security

March 25, 2021 BI News and Info

SQL Server databases are under greater threat than ever. Cyberattacks are on the rise, and they’re becoming more sophisticated with each new generation. And these aren’t the only risks. Organizations must also guard against insider threats, whether carried out by disgruntled employees or those engaged in more nefarious activities. At the same time, organizations must contend with a growing number of compliance regulations, such as the General Data Protection Regulation (GDPR), Health Insurance Portability and Accountability Act (HIPAA), and the California Consumer Privacy Act (CCPA).

To meet today’s security and compliance challenges, SQL Server DBAs and other security professionals need to implement comprehensive protection plans that safeguard their data, databases, and the environments in which they operate. One of the most important steps they can take is to monitor their SQL Server instances, auditing events that can indicate a threat against sensitive data.

If you’re one of those people charged with monitoring a SQL Server environment, you should capture all metrics that can help reveal possible risks, whether failed logins, altered permissions, schema changes, or anything else. You might be tempted to audit every available metric just to play it safe, but such an approach comes with significant overhead. You need to focus on those events that are the most relevant to your circumstances, taking into account applicable regulations and the type of data being stored.

Although there is no one-size-fits-all solution when it comes to a monitoring strategy, there are specific areas of concern that you should consider when determining the best approach to auditing your SQL Server environments. In general, you should monitor SQL Server access controls, SQL Server objects and configurations, and data access behavior. Even though these are broad fields, they provide a good starting point for defining a monitoring plan based on your specific needs.

SQL Server access controls

SQL Server includes several internal mechanisms for authenticating users to a SQL Server instance and controlling their access to data, using a combination of security principals, securables, and permissions. Principals are individuals, groups, or processes that are granted access to a SQL Server instance at the server level or database level. Securables are the objects that make up the SQL Server environments, such as databases, schemas, tables, functions, logins, or roles. Permissions define the level of access that is granted or denied to principals on specific securables.

Monitoring SQL Server access should be an important part of any auditing strategy because it provides direct insight into who can view or modify sensitive data or whether there are any suspicious efforts to access the data. For example, monitoring failed login attempts can help you detect brute force attacks or other malicious behavior. But that is only one metric. You should also consider tracking the following access-related events:

Security principals being added or dropped, whether at the server level or database level
Members being added to or deleted from server or database roles
Permissions being granted or denied to server, database, and schema securables

Changes in any of these three areas can indicate attempts at fraud or data threats. Once the changes are made, your databases might be vulnerable to data exposure, compliance violations, or disruptions in services. For example, a cybercriminal might gain access to a SQL Server instance and then modify the permissions in order to bulk-copy data out of a database or to run a malicious stored procedure. Access control changes can also indicate that someone inside your organization is being careless or is up to no good.

By monitoring access controls, you can identify a wide range of potential risks. For example, someone might have enabled the sa account, modified permissions on the SQL Server service account, or added members to the db_owner or sysadmin roles. Whether these actions are carried out intentionally or through sloppiness, they can all put a database at risk.

The decision on which access controls to monitor might be driven in good part by prevailing regulations, which often have strict guidelines on what information you need to track, but overall security concerns should also be part of your thinking, even if you’re not subject to a specific set of rules.

SQL Server objects and configurations

In addition to tracking access controls, you should also monitor changes to SQL Server objects at the server, database, and schema levels. In this way, you can audit changes that can put sensitive data at risk in ways not apparent through monitoring access controls. For example, a compromised login might go undetected, but it’s not until you see schema changes such as a dropped table or added function that you realize something is wrong.

As with other aspects of SQL Server monitoring, the exact approach you take to auditing SQL Server objects will depend on applicable regulations and the type of data in your database. However, there are certainly objects that stand out above the rest.

For example, you might find it useful to track activity related to the database objects themselves, such as when a database is created, altered, or dropped or whether it’s been stopped, started, attached, detached, gone offline, or brought back online. You might even want to know if a database’s size has changed and, if so, how much. The goal is to always be on top of changes to the databases so that you know which ones have changed, how they were changed, who changed them, and when the changes occurred.

That said, you need to monitor more than just the database objects to determine whether there’s been any tampering. For a clearer picture, you should also track changes at a more granular level, especially when it comes to objects that store sensitive data. For example, you might want to audit changes to a database’s schema objects so you can see which ones have been recently created, altered, or dropped or have been assigned a new owner. This type of auditing can also help you detect database drift, which comes with its own security concerns.

In addition to monitoring SQL Server objects, it can also be useful to audit changes to configuration settings. For example, a cybercriminal might have gained high-level access to a SQL Server instance and enabled CLR in order to launch a malicious assembly. If you were alerted to that change, you might have enough time to prevent that assembly from running and doing any damage.

Even if configuration changes aren’t the result of an attack, they’re still worth knowing about. For instance, a DBA might have enabled the xp_cmdshell extended stored procedures on a production server to pull down data and then forgot to disable it. Although it wasn’t done with malicious intent, it could still have serious consequences.

Monitoring SQL Server for configuration changes at the server and database levels can help detect threats that might otherwise be missed, but this approach is only effective if you actually catch the change. It doesn’t tell you what the configuration settings are supposed to be or whether any current settings are wrong. For this reason, you might also need to establish a base of agreed-upon settings that you can refer to for the absolute truth—the type of information often stored in a database configuration management archive (DCMA).

Data access behavior

Monitoring changes to SQL Server objects and configurations is extremely important, as is tracking access controls, but they might still not be enough to catch all threats or be in compliance with current regulations. You might also need to track access to the data itself, especially if regulations require it. For example, you might have to audit all SELECT access to each table that contains sensitive data. In this way, you can establish an audit trail of who accessed what data and when that access occurred, details you must often provide during a compliance audit.

You should also consider monitoring other types of events related to data access. One example would be to track whether anyone has used the BCP utility to bulk copy data out of a table. Another example would be to determine whether someone has issued a BACKUP statement, which could indicate the presence of an illegal database copy. Any metrics that can point you to potential data compromise are worth serious consideration.

In some cases, you might need to track data modifications instead of or in addition to data access. Regulations such as HIPAA and the Sarbanes-Oxley Act (SOX) require that you protect sensitive data against wrongful deletions or modifications, whether accidental or intentional. If either of these regulations applies to you, you’ll need to track all data modifications and deletions in order to demonstrate compliance. To do this effectively, however, you should audit database-level object changes, as well as INSERT, UPDATE, and DELETE activity. You should also be able to determine how the data has changed so you can compare before and after values.

In addition to monitoring data access and modifications, it can also be important to track SQL errors, which can point to SQL injection attacks or other threats. Would-be hackers often issue multiple SQL statements against a database to deliberately trigger SQL errors in an effort to navigate the schema. For example, a hacker attempting to carry out injection attacks might try to access invalid objects or use incorrect SQL syntax to glean schema information from the error messages.

Other monitoring considerations

You can go a long way in protecting your SQL Server environment by monitoring access controls, objects and configurations, and data access behavior. But a complete monitoring solution must also take into account factors such as the following:

Applications that access data in SQL Server databases should be monitored for unusual usage patterns or business processes or for any other suspicious activity.
The Windows Server computer that hosts SQL Server should be monitored for suspicious activity. Cybercriminals might be able to gain access through server logins and then change server settings or compromise SQL Server database files.
In addition to monitoring the SQL Server environment, you should also audit the monitoring tools and processes themselves. You must be able to ensure that no one has tampered with the auditing process and that your monitoring solution is as secure as the SQL Server data.
Effective monitoring often includes the ability to detect unusual usage patterns in how the SQL Server instance is managed and how data is accessed and modified. For this, you might need to create baselines that represent acceptable patterns that can then be compared to the audited events.

Another important factor in monitoring SQL Server is to ensure you have the right tools in place to help you audit selected metrics as efficiently as possible. You should also be able to track trends and to be alerted to behavior that represents an immediate threat. SQL Server Audit provides a good tool for auditing events, but it comes with limitations. One of the biggest is that it’s built into SQL Server. An attacker that gains access to the platform could disable the auditing features and other security protections. With a third-party tool such as Redgate’s SQL Monitor, you get a system separate from SQL Server, along with advanced features that help simplify the monitoring process.

Implementing a SQL Server monitoring strategy is no small feat. It requires careful planning and ongoing diligence to ensure that you’re protecting your sensitive data and complying with applicable regulations. Your monitoring solution should meet your specific requirements based on your current circumstances, with the understanding that those circumstances can change. SQL Server monitoring should also be implemented in concert with other security measures to help guarantee that your systems and data are protected at all times. In today’s climate, data security must be a top priority, and monitoring SQL Server is one of the most important steps you take, but you have to do it right to protect your data to its fullest.

If you liked this article, you might also like SQL Server authentication methods, logins, and database users.

Let’s block ads! (Why?)

SQL – Simple Talk

AI Weekly: With RPA on the rise, security challenges remain

March 20, 2021 Big Data

Join Transform 2021 for the most important themes in enterprise AI & Data. Learn more.

Robotic process automation (RPA) is experiencing a watershed moment. Gartner estimates that 50% of U.S. health providers will invest in RPA over the next three years. Moreover, the overall RPA market is expected to grow by more than 7% annually over the next few years to reach $ 379.87 million by 2027, up from $ 182.8 million in 2019.

Switching repetitive tasks to RPA functions not only eliminates errors, it also garners significant cost savings. That’s because RPA addresses bottlenecks with workflows, data, and documentation while providing audit trails and reducing compliance expenses and risks. RPA can also boost legacy integration and record digitization and enable data-driven decisions and “path-to-cognitive” technologies, according to Technologent CIO and VP Mike McLaughlin.

“The definition of RPA can be simplified as the category of software that automates tasks traditionally performed by a human, using software robots that follow a specific set of rules and interact with existing systems via user interfaces,” McLaughlin explained to VentureBeat via email. “These software robots can replace repetitive tasks, do system integrations, and automate transactions from task level to enterprise level via scheduled orchestration.”

For example, San Jose-based RPA firm Automation Anywhere recently worked with a pharmaceutical company in Europe to accelerate the research and approval of COVID-19 vaccines by augmenting reporting. RPA startup UiPath has also assisted with efforts around the pandemic, for instance helping the U.S. Department of Homeland Security use software bots to perform coronavirus-related data analysis.

Deloitte reports organizations that have implemented and scaled RPA see a return on investment within 12 months. And according to Everest Group, top performers earned nearly 4 times on their RPA investments, while other enterprises earned almost double.

This isn’t to suggest that RPA is without challenges. The credentials enterprises grant to RPA technology are an access point for hackers. When dealing with hundreds to thousands of RPA robots with IDs connected to a network, each could become an attack vessel if identity-centric security practices aren’t applied.

“Without tracking the exact access being granted, organizations are allowing [RPA] workers to leave their most valuable asset out in the open — privileged credentials,” One Identity president and GM Bhagwat Swaroop told VentureBeat. “Bad actors are targeting privileged credentials to gain access and move laterally within the network. With 53% of breaches being linked to misused or overused privileged credentials, the unmonitored and unrestricted access of RPA makes it even more susceptible to a breach than its human counterparts. If the privileged access vulnerabilities of RPA aren’t addressed quickly, I predict there will be a significant amount of RPA breaches that occur over the next year.”

Part of the problem is that many RPA platforms don’t focus on solving security flaws. That’s because they’re optimized to increase productivity and because some security solutions are too costly to deploy and integrate with RPA, Swaroop says.

Of course, the first step to solving the RPA security dilemma is recognizing that there is one. Realizing that RPA workers have identities gives IT and security teams a head start to securing RPA technology prior to its implementation. Swaroop recommends that organizations extend their identity and governance administration (IGA) to focus on the “why” behind a task, rather than the “how.” Through a strong IGA process, companies adopting RPA can implement a zero trust model to manage all identities — from human to machine and application.

“Through IGA processes, enterprises have the correct access management controls to mitigate risks, such as privilege creep, orphaned accounts, and the exposure of passwords and secrets. By eliminating the gaping holes in companies’ current RPA strategies, IT and security teams can ensure cybercriminals can’t compromise the robot or, worse, infiltrate their network,” Swaroop said.

He also suggests putting in place a privileged access management (PAM) setup that can secure and govern RPA systems. PAM systems allow enterprises to secure, control, and audit credentials and privileges RPA technology uses without compromising the return on investment.

“When a digital worker needs privileged access, the robot can retrieve credentials automatically from a PAM system, without any exposure to the bot owners or developers,” Swaroop said. “This not only provides a full audit trail of which digital workers accessed what applications, but also provides individual accountability and proof that no one can obtain the password, in a noncompliant manner, without slowing down robotic operations.”

For AI coverage, send news tips to Khari Johnson and Kyle Wiggers — and be sure to subscribe to the AI Weekly newsletter and bookmark our AI channel, The Machine.

Thanks for reading,

Kyle Wiggers

AI Staff Writer

VentureBeat

VentureBeat’s mission is to be a digital town square for technical decision-makers to gain knowledge about transformative technology and transact.

Our site delivers essential information on data technologies and strategies to guide you as you lead your organizations. We invite you to become a member of our community, to access:

up-to-date information on the subjects of interest to you
our newsletters
gated thought-leader content and discounted access to our prized events, such as Transform 2021: Learn More
networking features, and more

Become a member

Let’s block ads! (Why?)

Big Data – VentureBeat

Power Apps portal: Security 101

March 10, 2021 Microsoft Dynamics CRM

If you’d like a deep dive into specific areas within Power Apps portals security, Microsoft Docs is the ultimate, so I am making no attempts to replace our existing documentation. That said, when we’re new to an area such as Power Apps portals, it can be helpful to get a higher level and snapshot view. Here I’ve broken it down into a 101-level primer.

How do users access the portal?

When planning for your portal deployment, one of the first questions that will inevitably be asked is– Will your end-users be logging into the portal and be treated as authenticated users, or will they be accessing anonymously as guests? Power Apps portal supports both scenarios, and you can even use a combination of the two. If you will be requiring or allowing users to register and login to the portal, various external identity providers are available for configuration.

Portals users are not Dynamics users!

The Power Apps portal is meant to extend your Dataverse data to an external audience— this means that a portal user is not the same as a Dynamics 365 / Model-driven app user. In fact, if you do have Dynamics 365 users that are using the portal, their security roles and business unit are not going to apply here.

The portal is on a separate security model from what Dynamics 365 administrators may be accustomed to. This is because Power Apps portal users are treated as contact records in Microsoft Dataverse. When a user registers for the portal –or signs in if they are using Azure AD– a contact record is created in Dataverse. If the contact record already exists in Dataverse, they can be invited to the portal using out of the box functionality. The contact record will then store the portal user’s identities and web roles.

Web Roles

To reiterate, Dynamics 365 security roles will not apply to the Power Apps portal! Granted, the general concept is not too different here. In the portals’ case, a user’s persona is defined by the Web Role(s) that they have been assigned. Web Roles themselves will not have permissions stored on its record. Rather, think of a web role as a container table for permissions. It is only the web role that is then directly assigned to users– or rather, contact records.

5670.pastedimage1614909447177v1 Power Apps portal: Security 101

Page Permissions

When it comes down to it, your portal is a website; it has web pages! Naturally, we are able to specify permissions at the web page level. We can use permissions to restrict user access to certain pages to those that are logged in or who may have a specific web role. If applied, a user who does not have access to a certain web page will neither be able to see the web page in the navigation menu nor will the user be able to navigate directly to its URL without it erroring out. In a hierarchical setting, page permissions can also be used to secure entire branches of a portal through inheritance.

3286.pastedimage1614909447225v2 Power Apps portal: Security 101

Table Permissions

If we’ve opted to implement the Power Apps portal, chances are that we have done so in order to extend our Dataverse data to an external audience. If that’s the case, then we need to ensure that we’re taking into account our security at the table level. Table permissions are what determine the CRUD actions that users can take on specific records. Yes, that means that we can indeed apply long sought-after record-level security here, which is done via Dataverse relationship definitions.

Realistically, you are going to be using a combination of page and table permissions. We may need to restrict users from specific web pages or branches of the portal. If web pages contain Dataverse table data, then we also need to ensure that said table has been secured with table permissions.

3362.pastedimage1614909447262v3 Power Apps portal: Security 101

Tip: Table permissions will not apply unless you check the box “Enable Table Permissions” on its respective table form and table list.

Skylar Shonblom

Let’s block ads! (Why?)

Dynamics 365 Customer Engagement in the Field

Attach2Dynamics Or SharePoint Security Sync – Choose your smart app for effective document management in Dynamics 365 CRM/Power Apps.

March 4, 2021 Microsoft Dynamics CRM

Document management lets users manage common document types, such as Word, Excel, PowerPoint, OneNote, and create folders to save and manage those documents. However, when it comes to document management, the native SharePoint integration within Dynamics 365 CRM is rather limited. Our productivity apps offer two solutions for document management within Dynamics 365 CRM/Power Apps –SharePoint Security Sync and Attach2Dynamics.

What does SharePoint Security Sync do?

SharePoint is a document management and storage system that has native integration with Microsoft Dynamics 365 CRM. With SharePoint Security Sync, an additional layer of security is also added to the base of SharePoint that allows it to sync the security privileges from Dynamics 365 CRM to SharePoint for ensuring secure and reliable access to confidential documents stored in SharePoint.

What does Attach2Dynamics do?

Attach2Dynamics is a popular document or attachment management productivity app that allows integration between Dynamics 365 CRM with cloud storage like Dropbox, SharePoint, and Azure Blob Storage.

SharePoint Security Sync Or Attach2Dynamics?

Each app addresses a particular problem and has its own set of functionality. Here is a quick list of features that can help you determine which app seems like the best fit for your requirements. This list of features for both Attach2Dynamics and SharePoint Security Sync is long, with each app purposeful in its own ways. Following are the key features of both.

Key Features of Attach2Dynamics

Native Integration with Multiple Cloud Storages:

The biggest advantage of Attach2Dynamics is that is allows native integration between CRM and the today’s leading cloud storages like Dropbox, SharePoint, and Azure Blob Storage! Users can choose their choice of cloud and start managing and storing attachments/documents in the integrated Cloud Storage using Attach2Dynamics.

Easy File Management:

Users can easily manage their files with simple-to-use features like Drag & Drop, Upload, Download, Copy Link, and more within the CRM. Attach2Dynamics has a very intuitive and user-friendly interface that allows users to simply drag and drop multiple files and folders from CRM to SharePoint to move/upload folders easily, as well as email the files as attachments or links of the documents.

Move/Copy Attachments:

Quickly move or copy attachments like Notes/Email/ Sales Literature to Dropbox, SharePoint or Azure Blob Storage in Real-Time. This enables users to have better control over their attachments.

Document Location:

There can be multiple locations within SharePoint Site for a record. Attach2Dynamics allows users to select which folder (Document Location) they want to upload the file using an easy-to-use dropdown UI. This makes document saving convenient, smart, and fast!

Bulk Migration:

Time saved is productivity earned. Attach2Dynamics allows users to migrate multiple documents at once. This bulk migration allows more space to be freed at once from Dynamics 365 CRM. While the files are moving, users can also see the details as well as the status of the migrating Emails, Notes, and Sales Literature Attachments.

Security Templates:

Security Templates allows to grant the necessary user privileges according to requirement. Actions like Download, Upload, Delete, Email, etc. can be granted using Security Templates. With the necessary permissions, a user can perform such actions from the configured cloud storage. This feature allows managers to maintain control by granting only the required access to user actions in configured cloud storage for selected files and folders.

Key Features of SharePoint Security Sync

SharePoint Security Sync has all the above features as Attach2Dynamics except the integration with other cloud storage. SharePoint Security Sync is focused on the native integration of SharePoint within Dynamics 365 CRM while expanding the default feature set. Following are the additional features it has including the above Attach2Dynamics’ features.

Auto-Sync Privileges:

The biggest advantage of SharePoint Security Sync is that it allows users to auto-sync security privileges in SharePoint upon changes in user privileges in Dynamics 365 CRM. This sync triggers in situations such as record sharing or adding users to a team. Further, another level of security is added to this because any addition or changes in the security role of a user in Dynamics 365 CRM will be replicated and synced in SharePoint immediately.

Modify Security Role:

Along with the provision to auto-sync privileges, users can also very easily modify the security roles in Dynamics 365 CRM that will be replicated in SharePoint. This enables an easy transition from beginner to advanced level of accessibility to users as well as a quick way for admins to manage and modify the security roles according to requirements.

SharePoint Security Sync Tool:

Users can save their time by using the SharePoint Security Sync Tool to sync previous records in bulk. This means that the syncing of security privileges in record will be quick and hassle-free. What if ever sync fails? SharePoint Security Sync is smart enough to understand and quickly intimates you with details of failed syncs so you can restart the sync with just one click.

DocuSign Integration:

Smart integrations are the true catalyst of productivity. E-signatures are a commonly required service today while dealing with documents and attachments. Therefore, SharePoint Security Sync integrates seamlessly with DocuSign, a popularly known eSignature app that allows users to sign and annotate documents in different formats like PDF, Word, Excel, Images, Text, and so on. This feature makes it simple for users to electronically sign their documents/attachments stored in SharePoint and skip all the hassles.

Which one is right for you?

The choice is ultimately on your business requirements, the only assurance we can give is that both the solutions do an excellent job at solving problems through their own feature sets.

Please go ahead and download 15 days free trial forAttach2Dynamics and SharePoint Security Sync from our website; both our apps are Preferred Solutions on the official Microsoft AppSource.

We hope the above list helps you make the decision easier. Further, even if you require a demo or any help to make that final decision, kindly contact crm@inogic.com any time for instant assistance!

So until next time –stay safe and choose wisely!

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

How Ubiq Security uses APIs to simplify data protection

November 28, 2020 Big Data

As cyberthreats continue to multiply, startups with tools to protect data are in high demand. But companies are now facing the growing complexity of managing security across their various data sources.

San Diego-based Ubiq Security believes APIs could play a key role in simplifying this task. The company hopes to encourage more developers and enterprises to build security directly into applications rather than looking for other services to plug the holes.

“How do you take the messy and complicated world of encryption and distill it down to a consumable, bite-sized chunk?” Ubiq CEO Wias Issa asked. “We built an entirely API-based platform that enables any developer of any skill set to be able to integrate encryption directly into an application without having any prior cryptography experience.”

Issa is a security veteran and said companies have generally been focused on security for their data storage systems. When they start layering applications on top, many developers find they haven’t built security into those products. In addition, the underlying storage is becoming a thicket of legacy and cloud-based solutions.

“You could have an Oracle database, an SQL Server, AWS storage, and then a Snowflake data warehouse,” Issa said. “You’ve got to go buy five or six different tools to do encryption on each one of those because they’re all structured differently.”

Even when encryption is included in the application, it can be poorly designed. Issa said cryptographic errors have typically been among the top three vulnerabilities in software applications over the past decade.

“When you’re a developer in 2020, you’re expected to know multiple languages, do front end, back end, full-stack development,” Issa said. “And on top of that, someone comes along and says, ‘Hey, can you do cryptography?’ And so the developer thinks, ‘How do I just get past this so I can go back to building a fantastic product and focusing on my day job?’ So key management is an area where developers either don’t understand it or don’t want to deal with it because it’s so complicated and so burdensome and, frankly, it’s very expensive to do.”

To cut through those challenges, Ubiq’s API-based developer platform lets developers simply include three lines of code that make two API calls. By handling encryption at the application layer with an API, the security works across all underlying storage systems as well.

“The application will handle all the encryption and decryption and simply hand the data in an encrypted state to the storage layer,” Issa said. “That allows them to not only have a better security posture but improve their threat model and reduce the overall time it takes to roll out an encryption plan.”

Customers can then use a dashboard to monitor their encryption and adjust policies without having to update code or even know the developer jargon. This, in turn, simplifies the management of encryption keys.

Lessons from the government

Among its more notable customers, Ubiq announced this year that it had signed deals with the United States Army and the U.S. Department of Homeland Security. While government buyers have their particular issues, in this case the military and civilian systems faced many of the same obstacles large enterprises encounter.

“The government is struggling with digital transformation,” Issa said. “They’re stuck on all these legacy systems, and they’re not able to innovate as fast as the adversaries. So you’re seeing the likes of Iran and Syria and China and Russia and other Eastern Bloc countries start to build these offensive cyber capabilities. All you need is an internet connection, a bunch of skilled, dedicated resources, and now an entire country’s military cyber capability can rapidly grow. We don’t want that to outpace the United States.”

Part of the obstacle here is systems that run across tangled legacy and cloud infrastructure and mix structured and unstructured data and a wide range of coding languages. While there have been big gains in terms of protecting the underlying storage, Issa said attackers have increasingly focused on vulnerabilities in the applications.

“Encryption is something that everybody knows they need to do, but applying it without tripping over yourself is hard to do,” Issa said. “They turned to us because they’ve got all these disparate data types and they have all these unique types of storage. The problem is how to apply a uniform encryption strategy across all those diverse datasets.”

Issa said the emergence of the API economy has made such solutions far more accepted among big enterprises. They see APIs in general as a faster, more efficient way to build in functionality. Issa said applying that philosophy to security seemed like a natural evolution that not only eases the task but improves overall security.

“One of the other traditional challenges with encryption is when you deploy it somewhere and it breaks something,” he said. “And then you can’t deploy it in some sectors because the system is old. So you just apply it in two areas and then realize you’ve only applied encryption to 30% of your infrastructure. We enable a much more uniform approach.”

Ubiq got a boost earlier this month with a $ 6.4 million seed round. Okapi Venture Capital led the round, which included investment from TenOneTen Ventures, Cove Fund, DLA Piper Venture, Volta Global, and Alexandria Venture Investments. Ubiq plans to use the money for product development, building relationships with developers, and marketing.

“Our core focus is going to be on growing the platform, getting customer input, and making sure that we’re making the changes that our customers are asking for so we can run a very resilient, useful platform,” he said.

Let’s block ads! (Why?)

Big Data – VentureBeat

DocuSign integration with SharePoint Security Sync for Dynamics 365 CRM

November 20, 2020 CRM News and Info

SharePoint Security Sync – one of our Preferred Apps on Microsoft AppSource – is a comprehensive solution for integrating Dynamics 365 CRM / Power Apps with SharePoint for document management. Now with its latest feature, SharePoint Security Sync has added yet another feather to its cap and is on the way of becoming one of the finest apps for managing and securing confidential data in SharePoint from within Dynamics 365 CRM.

So, what is this latest feature all about? (get a quick glance of the same in our teaser video)

With this new feature of SharePoint Security Sync, you can now avail the benefits of DocuSign to electronically sign Dynamics 365 CRM documents stored in SharePoint. Being a popular eSignature app, most of you would be already using DocuSign for your day-to-day business transactions. With SharePoint Security Sync and DocuSign integration, you can now make use of DocuSign features using our user-friendly UI to sign contracts/agreements stored in SharePoint sites without having to navigate from one UI to another.

Now, let’s see how you will benefit from using this feature in your day-to-day business activities.

Suppose you have both SharePoint Security Sync and DocuSign installed in your CRM. Now, to activate this feature all you have to do is to just check the field named ‘Enable DocuSign Integration’ while creating a new connector configuration in SharePoint Security Sync for your CRM.

Once you have enabled this option, DocuSign button will be displayed on our user-friendly UI for your ready use. You have to just select the document and click on the ‘DocuSign’ button to sign or send the document for signature to the customers. Once you click the button, you will get the following two options – Get Signature&Sign Document.

Choose either of the options and you will be then directed to the DocuSign UI where you can make use of all DocuSign features.

Quite handy feature, isn’t it?

You can now culminate your daily activities pertaining to signing documents/agreements for contract renewal or closing business deals easily with just a few clicks.

No more navigating from one UI to another!

Everything is now available for you at our UI for your ready use.

So, wait no more! Grab this opportunity to download and explore this latest feature of SharePoint Security Sync from our website or Microsoft AppSource for a trial period of 15 days.

Also, have a quick glance at our popular document management app for Dynamics 365 CRM/Power Apps – Attach2Dynamics – which currently supports Dropbox, SharePoint, and Azure Blob Storage.

For any of your document management requirements, feel free to contact us at crm@inogic.com

Until then – Stay Safe, Stay Healthy!

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

DBA in training: Security

October 18, 2020 BI News and Info

The series so far:

By this time, we have discussed the importance of understanding your environment. You should have an idea of which databases support what applications, the SQL Server versions they run on, and the relative importance of each database and instance to the business.

Now, let’s talk about protecting the company from losing data due to security flaws.

Database security is a sub-specialty in and of itself, and you can read in-depth work by people like Denny Cherry, Robert Sheldon, Chip Andrews and numerous blogs. This will be a general discussion to help you get started. To begin the journey of a thousand miles, though, I will give you the first steps, and let you refer to the experts to address your individual needs. I’ll start with the mile-high view and work my way down, which should help you to provide the best measure of protection to your environments.

Physical security: Who can walk in the door?

Start with your physical security. Who has access to your physical servers or data centers? You need this list, and you want it to be as small as humanly possible. If there is no list, make one! Be sure to put policies in place to review this list on a semi-annual basis, and to review procedures for monitoring and auditing physical server/data center access. You should know who was in your server room, when and why. If your physical security is ever breached, your monitoring and auditing procedures will be instrumental in catching the culprit. Data is a company’s most valuable asset. This is worth the investment.

If you are completely in the cloud, you may think you are safe. You aren’t. Even if your servers are not physically accessible, it doesn’t mean they cannot be accessed. Whatever your physical setup, you should at least do an annual (unannounced!) penetration testing to see just how physically secure your company is. A good “pen tester” is worth every penny, because they will show you where the holes are in your security, giving you time to fix them before trouble really hits. You may think, “But I have a small company, where everyone knows everyone! What good will that do?” You will probably be very surprised. You can easily find stories of employees who, out of politeness, held a door open for the person who “just happened” to walk in behind them, thinking they are a new employee (or not noticing that they are unfamiliar at all). Other stories abound of employees who were careless with leaving their monitors unlocked or their badges unsecured at their desks. Perhaps you have heard the one about the person who kept the password to their computer on a Post-It on the monitor (or in their top desk drawer, etc.). Before you think it won’t happen to you, know that pen testers are very good at what they do; people are tricked all the time! So please make this an important line item on your list and do it at least yearly. People can become complacent over time with a false sense of security, so keeping awareness high is key.

Data file security: Who can get in your files?

Now that you are addressing your physical security, start thinking about the security of your data files. Your Infrastructure team may be handling this already. If so, it would be helpful to have an idea of what those measures are. At a minimum, it would be good to know who has access to your data and log files at any level – even if you have encrypted all of your data. What measures are in place to detect and alert you to unauthorized access into your data files? If you aren’t aware of any such measures, now would be the best time to work with Infrastructure to put those steps in place if possible.

What about backups files? Make sure that they are protected just as well as the data and log files. You also need to think about copies of production that might be made for development, QA, or testing. Are backups given to developers to restore on their laptops? To make sure that down level copies are production are protected, think about using a tool like Redgate’s Data Masker to sanitize sensitive data.

You also want to know what measures are in place to protect against malware, bots and the like. If your company allows pretty much anyone to download anything they like, the risk of these threats is higher to your data. The best hackers can get in and out with hardly a trace through some pretty stringent conditions, so don’t make it easier for them. Be the one to suggest some controls to keep at-risk software out of your corporate environment.

Keep your SQL Servers as current as possible

There is a very good chance that you will find a mixture of instances of SQL Server where you work. This is usually due to what vendors will support for their applications. You then ask the vendor to be able to upgrade the SQL Server instance or compatibility level for their database, only to hear a flat-out “no”, or “If you do this, you do it at your own risk – we won’t support it.” Accepting defeat, you go on with life and pray they will be ready to upgrade their product soon. The only problem is that if you run into trouble with the instance and have to open a ticket with Microsoft, the first question from the support staff is going to be, “What version of SQL Server are you running?” If you are running an instance that is in support but not current, they will likely first tell you to patch your instance and bring it current. If an instance is out of support, you are on your own.

Worse, you are vulnerable.

A couple of years ago, the discovery of a couple of processor security bugs called Spectre/Meltdown set the IT world on fire. Microsoft quickly responded with patches – but the patches only went back to 2008. If you had a 2005 instance that was not running on a 2008 + Windows server, you were out of luck. This situation strongly argues for isolating databases for older applications that will not support an upgrade – or for considering the possibility of a different product.

Keeping your patching as up to date as possible is one of the best things you can do to protect your servers. By saying that, I am not advocating to throw the patch on production the day after release. Start by waiting a week or so after the patch comes out to check for reported issues. If there are going to be problems, you should begin to hear about them online within that period, or you will see that Microsoft rolls out a new patch almost immediately. Then begin with a dev environment (after taking a server snapshot for rollback, if you can). See how things go for a week or so. Then patch test (if you have a test environment), and slowly move your way up. By the time you are ready for production, you will have a well-tested patch.

You might be wondering by now how to know when new patches are available. I like https://sqlserverupdates.com/ for this. This part of the site does a great job getting you started with patching and answers some frequently asked questions. Try to build a patching schedule (at least four times a year) and ensure that you stick to it. Your servers will thank you.

If you happen to use SQL Monitor, you can also use the Estate pages to keep track of updates and the current version of all your SQL Server instances.

word image 3 DBA in training: Security

Server and database security

You have done what you can to keep the criminals out of your physical environment and your files. It is time to begin looking to your server and database security.

In SQL Server, you have logins and users. Logins are groups, service accounts or individuals who can get into your server. Users are groups, service accounts or individuals who can access one or more databases. One login can have more than one user name, but they can only use one user name per database. This basic query will get you the login information on your servers:

SELECT *

FROM sys.server_principals;

Querying sys.database_principals will show your database users:

SELECT *

FROM sys.database_principals;

Just as you can have logins who can access the server and users that can access one or more databases, you also have server-level roles, that apply to the whole server, and database-level roles and permissions, that apply only to their specific databases. Permissions may be given through role membership or individually. For instance, to give someone a server-level role:

ALTER SERVER ROLE sysadmin

ADD (DROP) MEMBER <member name here>;

To do a database-level role membership:

ALTER ROLE db_datareader

ADD (DROP) MEMBER <member name here>;

Alternatively, to grant a specific permission:

GRANT (DENY, REVOKE) SELECT ON dbo.Table to [user name here];

These operations can also be done easily through the GUI by going to the properties or a login or user, depending on your scope. Some DBAs prefer the GUI as it can help to reduce errors.

Granting permissions is easy. Keeping a tight lid on them is not so simple. You should regularly review who can do what on your servers. How often is a matter of how much time you have, but try to set a regular schedule if possible. Security monitoring and reporting (which we will discuss later on) can really help you here. Try to be one of the first to know if someone leaves the company, so that you can disable SQL Server access. This is not just a best practice, but also a practicality: disgruntled employees are responsible for some of the worst attacks, so staying on top of this could save you a very bad time later on.

Starting at the top: Do you know who has sysadmin on your servers? Whoever has sysadmin rights can do anything, including dropping databases and tables, among other disastrous things. These people could take the business down for a protracted length of time. There should be a documented list of who is in this server role and it should be very small, not to mention periodically reviewed.

Sysadmin may be the most dangerous permission, but it is not the only one. You want to know who has these, too:

serveradmin – Members of the serveradmin role can change your server configurations and shut it down.
securityadmin – Anyone in this role can assign most permissions and allow people on to your server in the first place.
processadmin – These guys can end anything running in SQL Server.
setupadmin – Members of setupadmin can spin up or remove linked servers with TSQL (but not using SSMS).
bulkadmin – Bulkadmins can bulk insert data.
diskadmin – Members of this role can manage your disk files.
dbcreator – Just what it says. Anyone in this role can create (but also alter or drop!) any database.
CONTROL SERVER – This is one level below sysadmin. There are some differences, but CONTROL SERVER encompasses all the roles listed above except for sysadmin.

Now, for the database-scoped roles:

db_owner – Can create and drop objects and change configuration and maintenance on the database.
db_securityadmin – Can modify custom role membership and manage permissions – including their own.
db_accessadmin – Can add/remove access for Windows and SQL Server logins/groups.
db_backupoperator – Can backup the database (possibly breaking your backup chains).
db_ddladmin – Can create objects (or remove them) in the database.
db_datawriter – Can INSERT, UPDATE or DELETE data in all user tables.
db_datareader – Can read anything in any user table.
db_denydatawriter – Cannot INSERT, UPDATE or DELETE data in any user table.
CONTROL DATABASE: Encompasses everything in the database roles above.

Note: Server and database roles are fixed by Microsoft and cannot be changed.

These roles apply to every database, but there are some roles that you will only find in the msdb database. The msdb is part of the system databases, which we will discuss in more depth in another article. The msdb controls all agent jobs and stores information on your backups, SQL Server Integration Services (SSIS), data collector, policy-based management and database mail information. The msdb is a unique database for this reason. Because of all of the things it stores and controls, msdb needs some special roles of its own:

db_ssisadmin – Can do anything to any package. Could possibly elevate their own permissions to sysadmin.
db_ssisltduser – Can enumerate all packages, but only view, execute, export, own or delete their own packages. May import packages.
db_ssisoperator – Can see, execute, export and enumerate all packages. Can execute any package in SQL Server Agent.
SQLAgentUserRole – Can create/modify/delete their own local jobs and schedules. They cannot control ownership, and their role does not extend beyond the server where their role membership exists. They can enable or disable their own jobs and schedules, and edit their properties, as well as execute, stop and start them. They cannot delete job history on their own jobs unless they are granted that permission.
SQLAgentReaderRole – Members of the SQLAgentUserRole, the SQLAgentReaderRole can create/modify/delete their own jobs and schedules. They can enable or disable their own jobs and schedules, and edit their properties, as well as execute, stop and start them. They cannot delete job history on their own jobs unless they are granted that permission.
SQLAgentOperatorRole – Also a member of SQLAgentUserRole, the SQLAgentOperator has all of the permissions of the User and Reader roles, and can also view properties for operators and proxies, and see available proxies and alerts. They can start, stop or execute any local job, and they can delete the job history. They have do not have access to the Error Logs.

Finding permissions

I may have frightened you enough by now that you do not even want to give db_datareader permissions. Good! There is a concept called the Principle of Least Privilege which (put succinctly) says not to give any more permissions than what is absolutely needed. So role membership should be sparing!

Now, you may be wondering: how do I find out what permissions everyone has? After all, permissions don’t (and shouldn’t) just come through role memberships, and you may be scaring yourself imagining what your users are doing in your databases – or worse, what they could do, without realizing the danger. For server-level roles, you can use this query from Books Online:

SELECT sys.server_role_members.role_principal_id,

role.name AS RoleName,

sys.server_role_members.member_principal_id,

member.name AS MemberName

FROM sys.server_role_members

JOIN sys.server_principals AS role

ON sys.server_role_members.role_principal_id = role.principal_id

JOIN sys.server_principals AS member

ON sys.server_role_members.member_principal_id = member.principal_id;

For the database permissions and roles, I have a script that can help. It’s based on one that I found on Stack Overflow some time ago, but I modified it to look throughout an entire instance and to allow me to query it by database(s) or user(s). That way, you can get all the permissions on an instance (or database(s)) for a user or group. It will take you all the way down to the column level.

Now, here is the script. It is not fast, especially if you run it with the WHERE clause commented out. You’ve been warned.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

IF OBJECT_ID(‘tempdb..#t’) IS NOT NULL

DROP TABLE #t;

CREATE TABLE #t

(

DatabaseName sysname NULL

, UserName sysname NULL

, UserType NVARCHAR(MAX) NULL

, DatabaseUserName sysname NULL

, Role sysname NULL

, PermissionType NVARCHAR(MAX) NULL

, PermissionState NVARCHAR(MAX) NULL

, ObjectType NVARCHAR(MAX) NULL

, ObjectName NVARCHAR(MAX) NULL

, ColumnName NVARCHAR(MAX) NULL

);

DECLARE @dbName sysname;

DECLARE @dbCursor CURSOR;

DECLARE @sql NVARCHAR(MAX);

SET @dbCursor = CURSOR FOR

SELECT name

FROM sys.databases

WHERE source_database_id IS NULL

AND database_id > 4

AND is_read_only = 0

AND state_desc = ‘ONLINE’

ORDER BY name;

OPEN @dbCursor;

FETCH NEXT FROM @dbCursor

INTO @dbName;

WHILE (@@FETCH_STATUS = 0)

BEGIN

SET @sql = N‘

USE [‘ + @dbName + N‘]

INSERT INTO #t

SELECT ‘‘[‘ + @dbName + N‘]’‘ As DatabaseName,

[UserName] = CASE princ.[type]

WHEN ‘‘S’‘ THEN princ.[name]

WHEN ‘‘U’‘ THEN ulogin.[name] COLLATE Latin1_General_CI_AI

WHEN ‘‘G’‘ THEN ulogin.[name] COLLATE Latin1_General_CI_AI

WHEN ‘‘R’‘ THEN ‘‘Database Role’‘

ELSE princ.[type]

END,

[UserType] = CASE princ.[type]

WHEN ‘‘S’‘ THEN ‘‘SQL User’‘

WHEN ‘‘U’‘ THEN ‘‘Windows User’‘

WHEN ‘‘G’‘ THEN ‘‘Windows Group’‘

WHEN ‘‘R’‘ THEN ‘‘Database Role’‘

ELSE princ.[type]

END,

[DatabaseUserName] = princ.[name],

[Role] = NULL,

[PermissionType] = perm.[permission_name],

[PermissionState] = perm.[state_desc],

[ObjectType] = obj.type_desc,–perm.[class_desc],

[ObjectName] = OBJECT_NAME(perm.major_id),

[ColumnName] = col.[name]

FROM sys.database_principals princ

LEFT OUTER JOIN sys.login_token ulogin ON princ.[sid] = ulogin.[sid]

LEFT OUTER JOIN sys.database_permissions perm

ON perm.[grantee_principal_id] = princ.[principal_id]

LEFT OUTER JOIN sys.columns col ON col.[object_id] = perm.major_id

AND col.[column_id] = perm.[minor_id]

LEFT OUTER JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]

WHERE princ.[type] IN ( ‘‘S’‘, ‘‘U’‘, ‘‘G’‘ )

UNION

SELECT ‘‘[‘ + @dbName + N‘]’‘ As DatabaseName,

[UserName] = CASE memberprinc.[type]

WHEN ‘‘S’‘ THEN memberprinc.[name]

WHEN ‘‘U’‘ THEN ulogin.[name] COLLATE Latin1_General_CI_AI

WHEN ‘‘G’‘ THEN ulogin.[name] COLLATE Latin1_General_CI_AI

END,

[UserType] = CASE memberprinc.[type]

WHEN ‘‘S’‘ THEN ‘‘SQL User’‘

WHEN ‘‘U’‘ THEN ‘‘Windows User’‘

WHEN ‘‘G’‘ THEN ‘‘Windows Group’‘

WHEN ‘‘R’‘ THEN ‘‘Database Role’‘

END,

[DatabaseUserName] = memberprinc.[name],

[Role] = roleprinc.[name],

[PermissionType] = perm.[permission_name],

[PermissionState] = perm.[state_desc],

[ObjectType] = obj.type_desc,

[ObjectName] = OBJECT_NAME(perm.major_id),

[ColumnName] = col.[name]

FROM sys.database_role_members members

INNER JOIN sys.database_principals roleprinc

ON roleprinc.[principal_id] = members.[role_principal_id]

INNER JOIN sys.database_principals memberprinc

ON memberprinc.[principal_id] = members.[member_principal_id]

LEFT OUTER JOIN sys.login_token ulogin

ON memberprinc.[sid] = ulogin.[sid]

LEFT OUTER JOIN sys.database_permissions perm

ON perm.[grantee_principal_id] = roleprinc.[principal_id]

LEFT OUTER JOIN sys.columns col ON col.[object_id] = perm.major_id

AND col.[column_id] = perm.[minor_id]

LEFT OUTER JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]

UNION

SELECT ‘‘[‘ + @dbName + N‘]’‘ As DatabaseName,

[UserName] = ‘‘{All Users}’‘,

[UserType] = ‘‘{All Users}’‘,

[DatabaseUserName] = ‘‘{All Users}’‘,

[Role] = roleprinc.[name],

[PermissionType] = perm.[permission_name],

[PermissionState] = perm.[state_desc],

[ObjectType] = obj.type_desc,–perm.[class_desc],

[ObjectName] = OBJECT_NAME(perm.major_id),

[ColumnName] = col.[name]

FROM sys.database_principals roleprinc

LEFT OUTER JOIN sys.database_permissions perm

ON perm.[grantee_principal_id] = roleprinc.[principal_id]

LEFT OUTER JOIN sys.columns col ON col.[object_id] = perm.major_id

AND col.[column_id] = perm.[minor_id]

INNER JOIN sys.objects obj ON obj.[object_id] = perm.[major_id]

WHERE roleprinc.[type] = ‘‘R’‘

AND roleprinc.[name] = ‘‘public’‘

AND obj.is_ms_shipped = 0′;

PRINT @sql;

EXECUTE sp_executesql @sql;

FETCH NEXT FROM @dbCursor

INTO @dbName;

END;

CLOSE @dbCursor;

DEALLOCATE @dbCursor;

SELECT @@SERVERNAME AS ServerName

, DatabaseName

, UserName

, UserType

, DatabaseUserName

, Role

, PermissionType

, PermissionState

, ObjectType

, ObjectName

, ColumnName

FROM #t

WHERE

–(DatabaseName = ‘[DatabaseNameHere]’

–AND

DatabaseUserName = ‘UserNameHere’

–OR (DatabaseName = ‘[DatabaseNameHere]’

–AND DatabaseUserName LIKE ‘%UserName’)

–OR (DatabaseName = ‘[DatabaseNameHere]’

–AND DatabaseUserName LIKE ‘%UserName’)

AND PermissionType <> ‘CONNECT’

ORDER BY DatabaseName

, DatabaseUserName;

REVERT;

If you want to get an overall picture of where things stand with all your databases, you might consider running this with the WHERE clause commented out, so you get everything. Between these two queries, you should begin to build a picture of who can do what.

The first thing you want to look for is sysadmin permissions. Whenever a user or an application insists that it needs sysadmin permissions, question it. Often, that is not the case. For instance, the account running the Agent jobs will need sysadmin. You as a DBA will sometimes need sysadmin (i.e., when working with replication, viewing error logs, reading audit logs in SSMS, etc.). Even then, you will want to restrict when you use those rights, so consider having an alternate account for yourself that has sysadmin for only those occasions, and give your regular account reduced permissions to allow you to do your everyday tasks. If nothing else, it will make you think about what you are doing when you use that sysadmin account and lessen the risk of you making a mistake. Try to narrow that sysadmin membership as low as you possibly can. I have spoken to DBAs who work at companies where everyone has sysadmin. That means every one of those people could, by either mistake or design, drop every table in the enterprise or mess the data up badly enough to bring the business to a halt. If that does not scare upper management, I don’t know what will.

Once you have reviewed the sysadmin membership list, look at the other server role permissions and narrow those down. Then go on to the database permissions. The ultimate goal is to classify your end users into logical groups and give the collective groups only the permissions they need rather than to do individual permissions. In that way, if a person leaves or switches job positions in the company, they can be removed or added to the groups they need without having to do individual permissions auditing. Keep records of your SQL Server users (including any linked server users and the distributor admin for replication) and their passwords securely stored and access to them tightly controlled.

Depending on the state of your instances and databases, this step can take a good deal of time. Be patient – and persistent. Your data’s safety depends on you.

Be your authentic(ated) self

In a perfect world, you would only use Windows authentication on SQL Server. Using Windows authentication means that Windows verifies the user’s credentials in order to log into SQL Server. It is the safer way to go, and what you should do whenever possible.

If you are not there yet, then try to minimize the number of SQL Server accounts you have. Enforce your password policies when you make the accounts and make them change their passwords at least yearly if you can. Disable the sa user if at all possible.

Security monitoring

Now that you know who should be able to do what, think about how you will know if someone is trying to do something on your servers that they shouldn’t. What if a pen tester tried to get into one of your SQL Servers – would you know? Write jobs that look for failed logins and alert you regularly. I am a huge fan of Brent Ozar’s First Responder Kit. Among many other issues, it will catch elevated permissions and other security-related concerns and put them in a table for you to review. You can then report or alert on that data to help keep yourself on top of things.

If you are not ready or able to buy security monitoring tools, you might check out the EPM Framework. It is a free and easy way to set policies in SQL Server and ensure compliance. There is a good course in Pluralsight that will walk you through everything you need to know, should you have questions.

If you do have SQL Monitor in place, you can monitor when an account is added to sysadmin, failed login attempts, or anything you can think of with a custom metric.

word image 4 DBA in training: Security

Dangerous coding practices

Some practices can undermine all the careful work you have done so far without proper precautions. While I do not recommend saying no to all of these out of hand, you should know the risks they can present, and how to use them properly. This list is by no means comprehensive but should get you started.

Dynamic SQL

As a DBA, I have written a metric ton of dynamic SQL over the years. What is dynamic SQL? It is when you use a mixture of parameters and strings to construct and run a query. Sometimes, it is the best way to get things done. I happen to like it for optimizing some problem queries as well. You saw me use it in the query above to get all the permissions on an instance.

The risk here lies not the use of dynamic SQL itself, but in using it wrong. What is the wrong way to write dynamic SQL? This can go off the rails in a few ways:

Using EXEC (string). This is only there for backward compatibility. Use EXECUTE sp_executesql instead. It can be much safer.
Using unparameterized statements.
Using dynamic SQL in ad-hoc statements.
Not reviewing code to find vulnerabilities

Dynamic SQL needs to be tightly controlled, preferably executed by a login-less user that has very tightly restricted permissions and parameters that are sanitized with checks for things like semicolons, double dashes, asterisks, pound signs and keywords like DROP, DELETE, ALTER, TRUNCATE, GRANT, FROM, UPDATE, CREATE, etc.

Why? Two words. SQL Injection.

Unfortunately, if your company is hacked because of SQL injection, the hackers will not be the first to be blamed. You will. Spare yourself a bad day at the office. Erland Sommarskog and Bert Wagner have some great explanations and demos to share if you want to learn more.

xp_cmdshell

Think of granting access to xp_cmdshell as granting access to SQL Server’s OS. Unless there is a good reason to use it (and your security around it is good), this should be disabled in the configuration settings. However, this still requires monitoring (even after disabling). Steve Stedman has a good demo explaining how xp_cmdshell can still be used after disabling it and some preventative measures you can take to reduce your risks.

Remote queries

Any time you allow one SQL Server instance to query another one, you have opened a potential vulnerability for a hacker to jump servers. You may or may not be able to say no to this. If you cannot, ensure that your linked server security is as good and controlled as it can be, and that your network security is as solid as possible, making it as close to impossible for the black hats and the script kiddies as you can. Certainly, try to avoid allowing anyone to do anything but read across a linked server.

CLR

CLR stands for Common Language Runtime. CLR is a way to host .NET code in SQL Server. The possible danger comes as an added vector into your instance and not necessarily knowing what code is executed. You will want to monitor for these (again, sp_Blitz will catch them) and check them to ensure they are expected (and performant).

User-owned schemas, jobs, databases (or anything)

SQL Server users shouldn’t own anything. They are there to work with data, not to own it. When the end-user inevitably leaves the company, at the very least, you won’t be able to drop their accounts until you fix this, but at worst, it gives them a level of access that they just do not need – and you don’t want. Make a low-privileged user without a login or a (disabled!) sa the owner of databases and database objects whenever you can. It’s also possible to rename the sa, disabled or not.

Auditing

SQL Server auditing has been available in all editions at the server level since 2012, and fine-grained auditing became available in all editions with the release of SQL Server 2016 SP1. Take advantage of this feature in SQL Server and keep those records so that you know who did what, when and why. They will be invaluable if something goes wrong, if you are asked who dropped that table, or if other mischief occurs. This article will walk you through it!

Were you born in a barn? Shut the door!

How many times did you hear your mom or dad say that to you when you were a kid? They didn’t want flies in the house (and they certainly didn’t want the electric bill). Do not give the criminals more ways of getting in your servers than you must. Disable SQL Server browser (unless you have more than one instance on a server, in which case it must be on). Additionally, do not install every available feature when you install SQL Server – only put on what you need. You can go back and easily install other features such as Analysis Services or SSIS later, but there is no reason to have them on and to maintain them if no one is using them.

From a different perspective, if you have any individual permissions or users in your databases, you need to know when that person leaves the company, and have their access removed as soon as possible. This includes either disabling or – preferably – dropping the login and user. You have to do both. Dropping the login doesn’t drop the user from the databases; you are better off writing a script to drop the user where it exists in all the databases, then finally dropping the login. This is where you will be glad you didn’t allow users to own objects, because you won’t run into the “Cannot drop schema because it is being referenced by object”, “The database principal owns a schema in the database and cannot be dropped” and the “Login granted one or more permissions” messages that can be painful to unwind after the fact.

What do I do if my company has a data breach?

What if, despite your best efforts, you find that there has been a data breach? As DBAs, we plan for backups to go bad, and we layer our backups to cover it. We plan for disaster recovery, and we have availability options to cover that scenario. Have you ever thought about what to do if you are hacked?

First, if there is not one already, collaborate with other teams to come up with a run book, just as you would for disaster recovery and start writing your steps down. If possible, a response team consisting of people from infrastructure or network services, the DBA team, and business management should spring into action if a suspected breach occurs. This same team should meet periodically to review security standards and practices, to identify holes and how best to fix them, and to ensure that your security is in line with best practices. The Federal Trade Commission has a good set of guidelines to help you get started with your run book. Have the names of some forensic investigators handy so that you can quickly contact them. You want to quickly stop the breach and prevent anything else from happening. Then you are going to be very thankful for all the auditing and monitoring you have set up because it will be vital information to help determine what happened, how it happened, and how to prevent it from happening again.

Conclusion

Believe it or not, this will only get you started. Your server security will never be perfectly where you want it to be. You will find it is an ongoing process – you will no sooner wind up one round of server hardening than new applications, or features come out, or new vulnerabilities are discovered, and you are starting all over again. This is one of the most important parts of the job, though – if the data is hacked or ransomed, it will either not be available, or it will be useless. This makes security worth every bit of time you devote to it.

Let’s block ads! (Why?)

SQL – Simple Talk

Tag Archives: Security

What is role-based security?

Fixed server roles

Database Roles

Predefined Server or Database Roles

Evolving encryption

FHE ready for its closeup

Next steps

VentureBeat

SQL Server access controls

SQL Server objects and configurations

Data access behavior

Other monitoring considerations

VentureBeat

Lessons from the government

Physical security: Who can walk in the door?

Data file security: Who can get in your files?

Keep your SQL Servers as current as possible

Server and database security

Finding permissions

Be your authentic(ated) self

Security monitoring

Dangerous coding practices

Dynamic SQL

xp_cmdshell

Remote queries

CLR

User-owned schemas, jobs, databases (or anything)

Auditing

Were you born in a barn? Shut the door!

What do I do if my company has a data breach?

Conclusion

Recent Posts

Categories

Archives