Home > Articles > Data > SQL Server

This chapter is from the book

This chapter is from the book

Artifact Analysis

Once back at the forensic lab, you prepare collected artifacts and imported them into an analysis database named Inv_308_Scenario for further investigation. The first analysis step after confirming unauthorized database access is to determine the level of access the account (or related accounts) has within the database server.


Earlier in your investigation, you identified that the MSmith account was the attacker's point of entry and was used to create the EASYACCESS login. Determining the level of access that these accounts have within the database server is a critical factor in meeting your investigation objective of determining whether the attacker was able to access the credit card data.

You execute the following syntax, which will return a list of notable permissions assigned to the MSmith login:

USE INV_308_Scenario
SET @LOGIN = 'MSmith'
SELECT '1) Server | Endpoint' as 'category', ssr.name as 'grantee', ssp.state_desc as
'assignment', ssp.permission_name, CASE WHEN ssp.class = 105 THEN RTRIM(ssp.class_desc)
+ '.' + CONVERT (VARCHAR, ssp.major_id) ELSE ssp.class_desc END  as 'object',
ssp.class_desc as 'object_type', ssi.name as 'grantor' FROM SERV_Perm ssp, SERV_Prin
ssr, SERV_Prin ssi WHERE ssp.grantee_principal_id = ssr.principal_id and
ssp.grantor_principal_id = ssi.principal_id and ssp.class = 105 and rtrim(ssr.name) IN
UNION ALL SELECT '2) Server | Login', name, 'N/A', CASE WHEN logn_sql.status = 1 THEN
'ENABLED' ELSE 'DISABLED' END, 'N/A', 'N/A', 'N/A' from logn_sql WHERE
rtrim(logn_sql.name) IN (@LOGIN, 'PUBLIC')
--Fixed Server Role Membership
UNION ALL SELECT '3) Server | Fixed-server role', syu.name, 'N/A', sys.name , 'N/A',
'N/A', 'N/A' from SERV_Prin syu, SERV_Prin sys, SERV_Rmem sym where
sym.member_principal_id  = syu.[principal_id] and sym.role_principal_id =
sys.[principal_id] and syu.name IN (@LOGIN, 'PUBLIC')
-- Database
UNION ALL SELECT '4) Database | User', sdr.name, CONVERT(VARCHAR, sde.state_desc),
CONVERT(VARCHAR, sde.permission_name), sde.class_desc, sde.class_desc, sdi.name from
DBSE_Prin  sdr, DBSE_Prin  sdi, DBSE_Perm sde where sde.grantee_principal_id =
sdr.principal_id and sde.grantor_principal_id = sdi.principal_id and class = 0 and
sdr.name IN (@LOGIN, 'PUBLIC')
--Database Role Membership
UNION ALL SELECT '5) Database | Fixed/User-defined role', syu.name, 'N/A', sys.name ,
'N/A', 'N/A', 'N/A' from DBSE_Prin syu, DBSE_Prin sys, DBSE_Rmem sym where
sym.member_principal_id  = syu.[principal_id] and sym.role_principal_id =
sys.[principal_id] and syu.sid IN ((select sid from LOGN_SQL where name = @LOGIN),
(select sid from LOGN_SQL where name = 'PUBLIC'))
-- Schema
UNION ALL SELECT '6) Schema | Schema', sdi.name as 'grantee', sde.state_desc,
sde.permission_name, sao.name, sde.class_desc, sdr.name as 'grantor' from DBSE_Perm
sde,  DBSE_Prin sdi, DBSE_Prin sdr, SCHM_Data sao where sdi.principal_id =
sde.grantee_principal_id and sdr.principal_id = sde.grantor_principal_id  and
sao.schema_id = sde.major_id and sde.class = 3 and sao.[database] = @DBNAME  and
sdi.sid = (select sid from LOGN_SQL where name = @LOGIN or name = 'PUBLIC')
--Database Object
UNION ALL SELECT '7) Schema | Object', dbr.name,  dbe.state_desc, dbe.permission_name,
RTRIM(scd.name) + '.' + CASE WHEN dbe.minor_id >0 THEN RTRIM(syo.name) + '.' + (select
name from CLDT_Data where ID = syo.object_id and syo.[database] = @DBNAME and colid =
dbe.minor_id)  ELSE syo.name END as 'Object', CASE WHEN dbe.minor_id >0 THEN
RTRIM(syo.type_desc) + '_COLUMN' ELSE RTRIM(syo.type_desc) END as 'type', dbi.name from
DOBJ_Data syo,SCHM_Data scd, DBSE_Prin dbr, DBSE_Prin dbi, DBSE_Perm dbe where
dbr.principal_id = dbe.grantee_principal_id and dbi.principal_id =
dbe.grantor_principal_id and syo.[database] = @DBNAME and scd.[database] = @DBNAME and
scd.schema_id = syo.schema_id and dbr.sid = (select sid from LOGN_SQL where name =
@LOGIN or name = 'PUBLIC') and dbe.major_id = syo.object_id order by category, grantee,

When you run the preceding syntax within your INV_308_Scenario database, you receive the results captured in Figure 11.4. This data reveals that the MSmith login has been granted permissions through server and database fixed-role membership.

Figure 11.4

Figure 11.4 Permissions granted to the MSmith login

Briefly reviewing the permission hierarchy, you make the following observations:

  • Category 1: The MSmith account has implied permission to connect to the SQL Server endpoint through membership in the public server role.
  • Category 2: The second stage in the hierarchy shows that MSmith has an enabled login account, which will allow this account access to the SQL Server instance.
  • Category 3: Membership in the securityadmin, serveradmin, and setupadmin roles grants the MSmith account the ability to assign server-wide permissions, alter the server and/or endpoints, change permissions on the server, and alter linked servers. (Refer to Chapters 2 and 8 for additional details about permissions.)
  • Category 4: The CONNECT permission gives the MSmith login access to the OnlineSales database.
  • Category 5: Fixed-database role membership in the db_datareader and db_datawriter roles grants the MSmith account read and write access within the OnlineSales database, including the Orders table—which contains sensitive information.

Next, you rerun your earlier executed syntax, this time using the EASYACCESS as the login, to determine the level of permission the EASYACCESS account had within the database. The results you receive, which are captured in Figure 11.5, show that no explicit or implied permissions were assigned to the EASYACCESS login.

Figure 11.5

Figure 11.5 Permissions granted to the EASYACCESS login

Because no permission has been explicitly denied to the MSmith login, and because the highest level of access is the permission assigned within Category 3 (fixed-server role membership), you check the permissions placed on encryption keys and certificates. These permissions will be the deciding factor in confirming whether the MSmith account has sufficient access within the database to access the credit card data.

Native SQL Server Encryption

As you discussed with the client at the beginning of the investigation, encryption is in use on the database server. The MSmith account, which you verified was compromised during the brute-force attack, is believed to have access to the encryption keys.

To observe a listing of keys gathered from the server during artifact collection, you execute the following syntax against the Inv_308_Scenario database. This syntax returns a list of all symmetric keys on the victim system:

Select name, create_date, modify_date, key_guid from dbse_semk

The results of this query, which are captured in Figure 11.6, show that in addition to the database master key, there are two encryption keys on the system. This finding is a good indicator that encryption is in use.

Figure 11.6

Figure 11.6 Identified symmetric keys on the victim system

Because native SQL Server encryption supports only the varbinary data type, to further confirm the use of encryption, you perform a search on all columns in use within the database using the varbinary data type. To do so, you execute the following syntax within the INV_308_Scenario database:

select dbj.name as 'object', dbj.type_desc as 'object_type', cdt.name as 'column_name',
st.name as 'datatype' FROM CLDT_Data cdt, systypes st, DOBJ_Data dbj where st.xusertype
= cdt.xusertype and dbj.object_id = cdt.id and st.name = 'Varbinary' and
dbj.is_ms_shipped = 0

Once this query is run, you receive the results captured in Figure 11.7. These results indicate that three tables use the varbinary data type.

Figure 11.7

Figure 11.7 Identified tables using the varbinary data type

The sysdiagrams table, although set with an is_ms_shipped value of 0, is not a user object; thus the client would not use it to store application data. With this caveat in mind, you determine that the only two tables using the varbinary data type are the Orders and ORDERSBKU tables. Earlier in the investigation, the client mentioned to you that the ORDERSBKU table did not contain production credit card information and that your investigation should focus on the Orders table. Therefore you exclude the ORDERSBKU table from the remainder of the investigation.

You've now confirmed that encrypted data is likely to be found within the CCNumber column of the Orders table. You execute the following statement to determine if the compromised database account has access to the encryption keys created on the victim server:

SELECT syk.name as 'key_name', class_desc, spr.name as 'db_user', permission_name,
state_desc, major_id from dbse_perm sdp, dbse_semk syk, dbse_prin spr where class IN
(24, 25, 26) and syk.symmetric_key_id = sdp.major_id and spr.principal_id =
sdp.grantee_principal_id and class = 24
ORDER BY key_name, spr.name, permission_name, state_desc

When this code is run, you receive the results captured in Figure 11.8. As seen in the figure, the MSmith login was denied VIEW DEFINITION permission on the CCProtect_Key. This login does, however, have VIEW DEFINITION permission on the CCProtect_NewKey along with another SQL Server user.

Figure 11.8

Figure 11.8 Symmetric key permissions

For a user to have adequate permissions to encrypt and decrypt data using an encryption key within SQL Server, the user would need to have any level of permission on the key granted to that user. As your findings in Figure 11.8 reveal, the VIEW DEFINITION permission on the CCProtect_NewKey would have allowed the attacker to decrypt or encrypt data encrypted by it. However, the CCProtect_Key is another symmetric key on the system to which the MSmith account has been explicitly denied access. Earlier the client stated that CCProtect_Key is the current key being used for encryption. You have now proved the compromised account did not have access to this key to decrypt the sensitive data.

The final objective of your investigation is to determine the actions performed by the unauthorized user within the database. This objective can be satisfied through activity reconstruction.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020