Shared folders
Database applications often import and export data contained in text files. This can happen from within stored procedure codes, SSIS packages or hard coded scripts within SQL Server jobs. Often these data files are located in shared folders in the database server’s file system. If you see such shared folders in your SQL Server machine, take a minute to check its permission level. It is common to see the “Everyone” group having full access to shared folders.
You may also want to explicitly check the folder permissions for directories holding data, backup or replication files.
Security Configuration and Analysis Tool
The Security Configuration and Analysis tool is an MMC snap-in that can make use of customised security templates (created as .INF files) to check the status of your server’s security. There are some standard security template files that ship with Windows by default (see figure below).
These templates are configured for domain controllers, member servers etc. There is no specific template that applies to SQL Server database servers. However as a standard practice, most companies have domain level policies that automatically overwrite any server specific changes on a regular interval. If your organisation has any such customised template that is used for member servers and the server is not automatically refreshed with the security policies, you can use that template to verify the current status of security.
You can invoke the Security Configuration and Analysis tool by starting the MMC application from the command prompt, then adding the relevant snap-in (see below).
Once you have the snap-in into place you can create a security database (not in the traditional sense of SQL Server database). This database will have all the details of the current Windows server security configurations. The database creation is a fairly straightforward process: all you have to do is right click on the snap-in under the MMC console root and choose “Open Database” and provide a name for it. Security databases have an extension of .sdb. Once you provide the name, you need to select the appropriate template file.
Next, you need to choose “Analyze Computer Now…” from the pop-up menu by right clicking on the snap-in again.
The application will take some time to analyse the security status of your server against the template. Once the analysis is complete, it will show you with visual flags where the current security status is not consistent with the template settings. In the example below, we had used the tool with the “hisecws.inf” template file.
Working with the Security Configuration and Analysis tool is primarily a server administrator or infrastructure engineer’s work – not a DBA’s. This is where you need to be a bit careful as not to give the impression that you are treading into the responsibility area of another person or department. If needed, work closely with your server administrators for this audit.