MS SQL Authentication
Now that we’ve gathered basic information
about the location of our target SQL servers,
the next step is to understand how Microsoft SQL authentication works,
especially when it’s integrated with Active Directory.
Authentication in MS SQL is implemented in two stages.
First, a traditional login is required.
This can be either an SQL server login
or we can use Windows account-based authentication.
The SQL server login is performed with local accounts
on each individual SQL server.
Windows authentication on the other hand, works through Kerberos
and allows any domain user to authenticate with a Ticket Granting Service ticket.
In this stage, the login is mapped to a database user account.
For example, we may perform a login with the built-in SQL server sa account,
which will map to the dbo user account.
If we perform a login with an account that has no associated SQL user account,
it will automatically be mapped to the built-in guest user account.
We’ve covered logins and user accounts,
but we also need to cover the concept of SQL roles.
A login such as sa, which is mapped to the dbo user,
will have the sysadmin role.
This essentially makes it an administrator of the SQL server.
On the other hand, a login that is mapped to the guest user
will get the public role.
If Windows authentication is enabled,
which is typically the case when the SQL server is integrated
with Active Directory,
we can authenticate through Kerberos,
meaning we do not need to specify a password.
To test this, let's create a C# console application
to authenticate against the SQL server running on dc01.
Then we’ll attempt to execute some basic SQL enumeration queries.
First, we open Visual Studio on the Windows 10 client machine,
and create a new C# console application called SQL.
To create a connection to an MS SQL server,
we'll use the SqlConnection class,
from the System.Data.SqlClient namespace.
The constructor for SqlConnection requires a ConnectionString as an argument.
The ConnectionString consists of several parts.
The most important parts are the hostname of the server and the database name.
In our case, we will connect to the database server on dc01.corp1.com.
Since we don’t know anything about the database server structure,
we need to select a database name that always exists.
The default database in MS SQL is called “master”.
Lastly, we must specify either the login and password
or choose Windows Authentication
with the “Integrated Security = True” setting.
We need to specify all three parts of the connection string,
which are separated by semicolons.
Once the SqlConnectio object has been created,
we use the Open method to initiate the connection.
If the connection attempt fails, an exception will occur.
To handle this, we’ll wrap it in a try-catch clause.
If the connection is successful, we report it with a message to the console
and subsequently close the connection.
Otherwise, we’ll report that and then exit the application.
To test this code, we select Release
and x64,
and then compile it.
Once compiled,
we execute Sql.exe from the Windows 10 client machine as the Offsec user.
According to the output, we have access to the database.
This type of access is often possible on MS SQL
because the Builtin\Users group has access by default,
and the Domain Users group is a member of Builtin\Users.
Since any domain account is a member of the Domain Users group,
we automatically have access.
Note that we do not need any credentials
since the authentication relies on the Kerberos protocol.
To complete this exercise,
let’s disclose the SQL login we used along with the SQL user we are mapped to.
In addition, we want to check which SQL server roles are available to us.
We will start with the SQL login.
Once we have the code for that,
the additional information will follow a similar coding pattern.
The SYSTEM_USER SQL variable
contains the name of the SQL login for the current session.
If we can execute the SQL command “SELECT SYSTEM_USER;”,
we should get the SQL login.
We can use the SqlCommand class
to execute an arbitrary SQL query from C# and capture the output.
Instantiating an object from this class requires two arguments:
the SQL query and the open connection to the SQL server.
Since we are already able to open a connection
to the SQL server with our previous code,
we can append the code.
Note that both SQL queries and C# statements
always terminate with a semicolon.
To execute the SQL query, we invoke the ExecuteReader method,
which forwards it to the SQL server and returns a SqlDataReader object.
Before we can gain access to the desired data, we must call the Read method,
which returns the result of the query.
After we have fetched the results of the SQL query,
we can access them from the SqlDataReader object using indexing,
where the array index specifies the
zero-based column ordinal in the retrieved data row.
Next we print the result to the console.
It’s important to invoke the Close method
on the SqlDataReader object to allow subsequent SQL queries to be executed.
If we don’t, the SQL connection will be blocked.
Once we have obtained our login, we want to determine the username it is mapped to.
We’ll do this with the USER_NAME() function.
This is very similar to what we did with SYSTEM_USER.
Finally, the IS_SRVROLEMEMBER function
can be used to determine if a specific login is a member of a server role.
The IS_SRVROLEMEMBER function accepts the name of the role
and returns a boolean value.
In this case,
we’re determining whether our login is a member of the public role.
We can use a similar method to discover any other role memberships.
Our current code checks the SQL login, the username,
and for membership of the public and sysadmin roles.
Let’s try it out.
From the output of our console application,
we note that we logged in with our domain account,
which is mapped to the guest user account.
Additionally, we have the public role,
but not sysadmin.
While this is a low privilege access,
it’s important to note that we have access to the database
and can execute SQL commands,
all without requiring the password of our current user.
In the rest of this module,
we are going to expand our access beyond the database instance
to the underlying operating system and additional servers.