Always good to know your Guest (SQL Guest User)

Few days back came across an interesting issue, which was new for me. Application owner buzzed me and raised concerns on security issue.

App.Owner: We have some serious issue with our database security. I fear wrong people having elevated access to our data.
Me: Ok, let me have quick look and get back to you.
App.Owner: Yes please. We need to fix it now.
Me: Did a quick check and everything seems to be fine. You’re ID with db_owner and other users with read access to the database.
App.Owner: I was also under the same impression. But today came to other users were also are also able to modify the data. That’s not at all good.
Me: hmm that’s strange. I will do a complete check to figure out the leakage.
App.Owner: Thanks. I will wait to hear from you.
Me: Okay. Ahh Found the suspect. All those privileges were getting transmitted from guest user. I could see someone had mistakenly provided extra permission to guest user, which is not at all required for this environment. Now things are back to normal. Please have a look from your end and let me know you if you have any questions
App.Owner: Great! Thanks for fixing this. Have a nice day
Me: You too, have a nice day.

In my case issue was with guest user permission. Let’s see the same in demo

-First we will create a new database named ‘TESTDB’. Then will check the status of guest account.

Note: If guest user is enabled in Model database, the same will get applied to newly created database

-Now we will create a SQL Login, a new table ‘EPL’ and insert 3 records.

We have just created a SQL Login it’s not mapped to any of the database and instance level also no permission is provided.

Now let’s try accessing the data in TESTDB using UserA.

As expected, ‘UserA’ is not able to fetch the data from TESTDB. Now let’s try enabling and providing read and write permission to ‘Guest’ user in TESTDB. You cannot enable/disable Guest user through GUI, it can be done only with T-SQL.

To Enable Guest Account:
 
USE [DATABASE_NAME]
GO
GRANT CONNECT TO GUEST
GO
 
To Disable Guest Account:

USE [DATABASE_NAME]
GO
REVOKE CONNECT TO GUEST
GO

Command completed successfully. Now let’s try

 
 So now UserA is able to read and insert records in TESTDB. The same task can be performed by all users associated with the instance. Guest user when enabled will authorize access for logins which are not mapped to the database.

Things to know about Guest user

• It’s always recommended to disable guest user for user database, unless you have any specific/special requirement.
• Guest user cannot be dropped, but can be disabled/enabled.
• Guest user cannot be disabled for Master and Tempdb database.
• Msdb database guest user should not be disabled. If you have any plans to do so, please read this.

Cheers,
Naveen

Automating Standalone SQL Installation

Few days back I started working on Automating or Standardizing SQL Installation. Ultimate goal is to reduce the manual work. Unattended installation using configuration file will help us to achieve this. 

First let's see how to get our configuration file. First time you have to run the setup manually and pass all the parameters.  You have move till “Ready to install” page in the installation wizard. There you will get the configurationfile.ini location.

Copy the configurationfile.ini to the location where SQL setup file is placed (It’s not mandatory to do this. If you place the configuration file in different location, same location should be passed while executing the setup).

So now your configuration file is ready. Make the below changes in configuration file to perform unattended installation.

   -Change QUIET="False" to QUIET="True"

 

   -Remove UIMODE="Normal" from configurationfile.ini

Now save and close the file. Please note, configuration file will not store/have password details of any of the accounts (SQL Service Account, SQL Agent Account and SA Password etc.). So we have to pass this information explicitly while running setup in command line. And also we should pass a parameter /IACCEPTSQLSERVERLICENSETERMS=”True” to accept the SQL Software license.

Below is the script to do unattended installation.

***********************************************************************************

setup.exe /ConfigurationFile=ConfigurationFile.ini /IACCEPTSQLSERVERLICENSETERMS=”True” /SAPWD=”SaAccountPassword” /SQLSVCPASSWORD=”SQLServiceAccountPassword” /AGTSVCPASSWORD=”AgentAccountPassword”

***********************************************************************************

Note: SA password is not required for Windows Authentication

You can create a batch file and run it to perform unattended SQL installation. This will be advantageous if you have to in setup SQL in multiple servers with same configuration.

But what if you have a requirement to install SQL in multiple server with different instance name and collation settings?

Above script will not work in that case. However if you could go and make required changes to the configuration file every time will work. But again that will be time consuming task to make changes in all servers. Other option would be passing the parameters explicitly while running the setup. So here also lot of editing will be required.

To overcome all these difficulties, I wrote a small dynamic script where we have to define few parameters based on our requirement and those parameters will be taken as input while running the setup.

I have just added below parameters in my script to be taken as input.

      -SQL Instance Name

       -SQL Collation

       -SQL Service Account Name and Password

       -SQL Agent Account Name and Password

       -SA Account password

       - To accept SQL Server License Terms

Assuming in most of the case install directory, root instance directory and other directory will be standard. So initially whatever you have passed in the configuration file will be taken.

You may need to change the below script according to your requirement.

***********************************************************************************

@echo off

color 02

echo===========================================

echo Unattended SQL Installation

echo===========================================

color 05

set /p LI=Do You Accept SQL Server License Terms(True/False):

set /p IN=Enter SQL Instance Name:

set /p SYS=Enter Sysadmin Account Name:

set /p SA=Enter SA Account Password:

set /p SVN=Enter SQL Service Account Name:

set /p SVP=Enter SQL Service Account Password:

set /p AGN=Enter SLQ Agent Account Name:

set /p AGP=Enter SQL Agent Account Password:

set /p COL=Enter SQL Collation:

color 09

echo===========================================

Echo Starting SQL Server Installation....

echo===========================================

date/t

time/t

setup.exe /ConfigurationFile=ConfigurationFile.ini /IACCEPTSQLSERVERLICENSETERMS=%LI% /INSTANCENAME=%IN% /INSTANCEID=%IN% /SQLSYSADMINACCOUNTS=%SYS% /SQLCOLLATION=%COL% /SAPWD=%SA% /SQLSVCACCOUNT=%SVN% /SQLSVCPASSWORD=%SVP% /AGTSVCACCOUNT=%AGN% /AGTSVCPASSWORD=%AGP%

color 02

echo Batch file completed

date /t

time /t

***********************************************************************************

Now all you have to do is prepare your configuration file and save the above script in .bat format. Place both the files where SQL Setup is placed.

That’s it, open command prompt with “Run as administrator”, run the batch file, provide required inputs and your SQL installation will start. 

Below is the screenshot of SQL installation in my system.

 

You will get the start and end time of installation. To make it bit sparkling added few colors in between the code.  So on each phase of batch execution you would notice a change in color!!!

To learn more about Unattended SQL Installation click here.

Hope this helps in Automating SQL Installation :-)

Cheers,

Naveen