A community of more than 1,600,000 database professionals and growing

Featured Script

Accurate Time between Two Dates in Year, Month,Day Format

The Voice of the DBA

Track Your Consultants

If you pay attention to security issues in software, you've probably heard about man-in-the-middle attacks. These can occur more frequently than you expect, though inside of a company, it's more unlikely that you'll experience one if you have some fairly basic security controls on your network.

However, if you invite someone inside of your network, such as a consultant of some sort, you should be more vigilent. There's a great post showing how someone can execute a MITM attack against SQL Server. It's in depth, showing exactly how packet captures lead to the ability to hijack a session and create a new login.

If this seems like a lot of work, it is. However once the attack is built, this could easily be run by anyone inside of your network. I could see consultants running this type of attack and storing credentials they've created for use many months later. These could be sold later to someone that might use them in a website or other application to gather data from outside of your network.

Defending against these types of attacks is hard. Certainly not allowing free access for consultants is key, though a quick command line execution of a script might not be something that's easily noticed. At the very least, sysadmin logins should be monitored, and any changes to this list investigated immediately. However, I'd also say any built in server role changes should be checked and verified as being valid alterations. Even your SQL logins shouldn't change without administrators being aware.

Monitoring your systems is a big part of security. You might not prevent many of the attacks, but knowing they've taken place allows you to respond and potentially protect sensitive data.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

Everyday Jones

Follow Steve Jones on Twitter to find links and database related items and announcements.

Want to use Git with your SQL Server database?

SQL Source Control now has built-in support for Git. Inside SQL Server Management Studio, you can commit to Git, push & pull changes from remote repositories, and get an object-level history for your database. Learn more.

Hate explaining your database in meetings?

SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.

Have you got SQL Fingers?

Watch these free SQL Prompt tips videos for SQL writing hints from top SQL Server MVPs. SQL Prompt is the SQL code productivity add-in for SQL Server Management Studio and Visual Studio. Find out how easily you can write SQL.

Featured Contents

Two Things I Would Change in SSISDB Setup

Ben Kubicek from SQLServerCentral.com

I suggest these two changes to the SSISDB setup to prevent issues down the road. More »

Webinar - Redgate DLM Demo (with TFS, TFS Build & Microsoft Release Management)

Press Release from SQLServerCentral.com

Redgate’s Arneh Eskandari and Microsoft SQL Server MVP, Steve Jones, will show you how Redgate’s DLM (Database Lifecycle Management) solution works to improve your database development and deployment processes. Today 12noon EDT More »

Introduction to Protecting SQL Server with Azure Site Recovery

Additional Articles from Database Journal

Microsoft cloud platform allows you to provide resiliency for your on-premises SQL Server deployments. Marcin Policht describes this functionality, available as part of the Azure Site Recovery solution. More »

From the SQLServerCentral Blogs - ETL Auditing

Tim Mitchell from SQLServerCentral Blogs

It happens far too often: Once an ETL process has been tested and executes successfully, there are no further checks... More »

From the SQLServerCentral Blogs - SQLBits in Space

Steve Jones from SQLServerCentral Blogs

It’s coming in May. The official UK SQL Server 2016 launch event is SQL Bits and the conference returns to... More »

Question of the Day

Today's Question (by Peter McLean):

What happens when running the following script?

 CREATE TABLE dbo.PkTest ( NameVARCHAR(5) NOT NULL ) INSERT INTO dbo.PkTest (Name) VALUES('A1234') INSERT INTO dbo.PkTest (Name) VALUES('A123 ') INSERT INTO dbo.PkTest (Name) VALUES('A123') SELECTDISTINCT Name FROMdbo.PkTest ALTER TABLE dbo.PkTest ADD CONSTRAINT [PK_PkTest] PRIMARY KEY CLUSTERED (Name)

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: T-SQL.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

SQL Server T-SQL Recipes

SQL Server T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server. This edition has been lightly updated for SQL Server 2014 and provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, create in-memory tables and stored procedures, insert and update data, generate reports, secure your data, and more. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I am working with Row Level Security and have a function that determines if a particular user should have access to rows in a table. My function looks like this:

 CREATE FUNCTION DBO.RLS_Customer_Check (@username AS sysname) RETURNS TABLE WITH SCHEMABINDING AS   RETURN SELECT 1 AS RLS_Customer_Check_Result      WHERE @username = USER_NAME() GO

I have a table, CustomerAccessList, with a column for CustomerID and UserName. I add a filter predicate as follows:

 CREATE SECURITY POLICY rls_CustomerFilter ADD FILTER PREDICATE dbo.rls_customer_check(customerid) ON dbo.CustomerAccessList; GO

The bsmith user can only view the rows that have "bsmith" in the username column. The current table looks like this:

 CustomerID UserName ---------- -------- 1 bsmith 2 sjones 3 bsmith

What happens when bsmith runs this batch?

 INSERT dbo.CustomerAccessList ( CustomerID, username ) VALUES ( 5 -- CustomerID , 'sjones' -- username ); select * from CustomerAccessList

Answer: The insert succeeds and 2 rows are returned from the SELECT

Explanation:

A FILTER predicate for Row Level Security does not block inserts, even if those inserts will be subsequently filtered by the security policy.

Ref: Row Level Security - https://msdn.microsoft.com/en-us/library/dn765131.aspx

» Discuss this question and answer on the forums

Featured Script

Accurate Time between Two Dates in Year, Month,Day Format

Vimal Lohani from SQLServerCentral.com

This is a function, please follow the comments on script to use it.

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2014 : Administration - SQL Server 2014

SSIS on a dedicated server? - We have a data warehouse SQL server and it has a lot of ETL SSIS running nightly on the server...

SQL Server 2014 : Development - SQL Server 2014

OMG - What have they done to Stored Proc Scripts??? - Just upgraded to SQL 2014 development environment. Please tell me how I can convert back to the old way of...

Running Totals with window function - I have a business requirement to produce a running total, but with a twist ... If the running total drops below...

SQL Server 2012 : SQL 2012 - General

MSDTC / AlwaysOn - We just implemented Always on for a couple of servers, and I am getting the following error when trying to...

Login can't be deleted because it granted permissions - I'm trying to delete an individual login from a server but am failing because the login "has granted one or...

SQL Server 2012 : SQL Server 2012 - T-SQL

Remove/Keep record based on group - Hi, I have the following data: declare @Policy table ( Download varchar(20), AccountNum varchar(20), RootPolicyNum varchar(20), PolicyNum varchar(20), CurrentStatus varchar(20) ) insert into @Policy (Download, AccountNum, RootPolicyNum, PolicyNum, CurrentStatus) select 'EQAutralia',...

Need help with datatype Time conversion - Hello All, I have source table with column nvarchar(20). the data looks like below. [b]Duration[/b] [b] 120:06:31.150 120:06:31.165 238:21:19.875 266:45:41.472 287:03:25.184 [/b] I...

indexes-non clustered - hi all how to sort non clustered indexes ? any suggestion on this

SQL Server 2008 : SQL Server 2008 - General

Need help on getting last day of the months in seconds for 13 months - I wrote the query and it returned the results only on the 28th of each month. [code="sql"] With cteMonths as ( select DATEADD(s, -1,...

Scalar function - Hi I need some help with this function below. I've not been able to get hold of my developer. In the db he...

INSERT Statement - How to Handle to Avoid Thousands - I have a table with the following four columns: [ProjectType] [ProjectSubType] [ProjectCategory] [ProjectSubCategory] I need to perform an INSERT statement of...

SQL 2008 database replication - Dear all, we have an installation with sql 2008 express, without Internet connection. What we need is to replicate the database...

SPSS .SAV File - How to open from SQL Server - I have a ".Sav" file in SPSS (Statistical Package for the Social Sciences). I'm trying to read the data using...

SQL Server 2008 : T-SQL (SS2K8)

sql and stored procedure - How can I return select @@servername along with the result of this stored procedure - msdb.dbo.sp_get_sqlagent_properties in a single row. Result : Servername1,all columns...

Query Performance Issue with multiple unions and a view - Hello Gurus, I have a case where instead of looking too much into the indexes(Tables are not that big), I am...

SQL Server 2008 : SQL Server 2008 Administration

Querying multiple databases - Hello -- We have twenty databases utilized by an application, and I want to be able to automatically query all the...

Need the query for system Information - Hi All, I want to prepare the database that needs to be capture Memory,CPU usage and disk space utilization from all...

How to find out what databases have not being used for a year - Hello, I started working in a company where we have 6 production servers with a lot of databases on each...

SQL Server 2005 : Administering

Script to Change Collation of User Database - Hello, Is their is any Script to change collation of user database ? The process Which I know; First Backup the User database 1.creation...

SQL Server 2005 : Development

How to show the column wise data into row wise - Hi, i want to show the column data in row wise, can any one please help me how to write a...

This email has been sent to [email protected]. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the [email protected].

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.