Reviewing Linux files via GUI on Ubuntu

On Linux the SQL Server vNext files are stored under C:\var\opt\mssql and you may not be able to view/access them due to lack of permissions.
 
From a terminal prompt run the following:

  • sudo apt install gksu
  • gksu nautilus

 
You will now be able to search the folders and view things like the errorlog using gedit:
image1

Installing SQL Server vNext on Ubuntu using Hyper-V

The following steps were used to create a test Hyper-V virtual machine (VM) with SQL Server vNext on Ubuntu 16.04.2 TLS Desktop.
 
Prerequisites:

  • Create a Generation 1 VM
  • Ensure the VM has at least 3.25GB of memory allocated or the install of SQL Server vNext will fail
  • Ensure the VM has an internet connection
  • Download and add the ubuntu-16.04.2-desktop-amd64.iso from https://www.ubuntu.com/download/desktop to the VM as a DVD
  • Start the VM

After the install of Ubuntu you may get a message to remove the Ubuntu media. Simply turning off/on the VM should resolve this.
 
Here were the commands to install SQL Server vNext:
 

sudo apt install curl

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

sudo apt-get update

sudo apt-get install -y mssql-server

sudo /opt/mssql/bin/mssql-conf setup

systemctl status mssql-server

curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

sudo apt-get update

sudo apt-get install mssql-tools

sudo ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd

sqlcmd -S localhost -U sa -P '$trongPa55'

AKAWN_vNext_Ubuntu

Function sys.dm_os_enumerate_filesystem

While reviewing SQL Server vNext I came across the sys.dm_os_enumerate_filesystem function.
 
The function takes the following 2 parameters

  • @initial_directory nvarchar(255)
  • @search_pattern nvarchar(255)

It appears to be extremely powerful and currently outputs the following columns:

  • full_filesystem_path
  • parent_directory
  • file_or_directory_name
  • level
  • is_directory
  • is_read_only
  • is_system
  • is_hidden
  • has_integrity_stream
  • is_temporary
  • is_sparse
  • creation_time
  • last_access_time
  • last_write_time
  • size_in_bytes

Below is sample extract when using
SELECT * FROM sys.dm_os_enumerate_filesystem('c:\','*')

image1

The command doesn’t appear to work 100% at this stage on Linux. I received the following info and then it errored:
image2

Msg 3634, Level 16, State 1, Line 7
The operating system returned the error '0x80070002(The system cannot find the file specified.)' while attempting 'GetNextStream' on '(null)'.

image3

Failed sysadmin Permission Elevation Attempts

Consider this scenario.
 
You have a user called Bob who thinks today is a good day to become a sysadmin.
 
He runs the following command to escalate his privileges:
 
ALTER SERVER ROLE [sysadmin] ADD MEMBER [bob];
 
Thankfully this fails and world domination will need to wait another day.
 
image1
Now as a DBA you’ll be thinking, this activity will be all over the SQL ERRORLOG and/or Windows Event logs so you can have a chat with Bob about his intensions, just as you would with login failures.
 
Allais, there is nothing recorded in either of these.
 
Fortunately, the Default Trace does capture this event and you can have that chat with Bob after all.
 

SELECT StartTime, TextData, ApplicationName, LoginName
,CASE WHEN a.Success = 0 THEN 'No' ELSE 'Yes' END AS 'Was Successful'
FROM master.sys.fn_trace_gettable(
( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) + 'log.trc'
FROM  sys.traces
WHERE is_default = 1
), DEFAULT) a
WHERE a.Success = 0

 
image2