Category: Security

Stephen West has a post on creating Windows firewall rules to allow inbound traffic for a SQL Server instance:

For Static Port:

• Go to Start>Run and type WF.msc and then click on OK button

• Under the Windows Firewall with Advanced Security, right-click on Inbound Rules, and then click on New Rule

• In the Rule Type box, select the option Port, and then click on Next button

• In the dialog box of Port, select the option TCP. Then, select the option Specific local ports, after that type the port number 1433 for the static instance. After that click on Next button

• Select Allow the action under the Action dialog box and then click on Next button

• Now, Under the Profile dialog box, select any profiles which you want to connect to the SQL server, and then click on Next button

• Type a name and description of the rule, in the Name dialog box and then click on Finish button

Read on for dynamic ports.  I feel like I need to throw out all kinds of warnings about not exposing a SQL Server instance directly to the public internet.

Jana Sattainathan explains how to get a user’s effective permissions:

Microsoft has provided a function named fn_my_permissions that gets the permissions for you. It has multiple classes of objects for which you can get permissions. We already know about SERVER, DATABASE and OBJECT but there are a lot more. To get that list of classes, let us use fn_builtin_permissions function

The one thing I wish this function did was let a sysadmin see another user’s effective permissions without using EXECUTE AS; I’d like to be able to apply that function to sys.logins and get an exploded list for reporting.

Tom Norman has a script to change the certificate used for TDE:

When you create TDE certificate by default, the certificate is just created for a year. Depending upon your auditor, you may be asked to change this TDE certificate yearly.  In this blog post, you can watch when your TDE certificate expires using policy-based management, https://www.mssqltips.com/sqlservertip/2388/internal-sql-server-certificates-have-expired-is-this-an-issue/.

Click through for the script.

Artemakis Artemiou hits a controversial topic in security today:

As you can see from the above screenshot, the maximum password age is set to 90 days. This means that if I have a login that uses SQL Server Authentication or a contained database user with a password, if the login’s or user’s password was last changed 90 days (or more) ago, then the login/user will be prompted by SQL Server to change her password.

Even though this practice was used for many years, not only in SQL Server but similarly, on Windows-level, as well as in other systems and applications, recent studies argue that it should not be a recommended practice anymore. On the contrary, these studies suggest that user passwords should not be regularly changed but rather change only when there is a specific and justified reason to do so. Furthermore, new security standards are being formulated that contain new recommendations on password change.

Security is all about trade-offs.  If you make people change their passwords every 90 days, they’ll write the password down somewhere.