The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

When giving advice to, or doing an audit for, a SQL Server shop, I do a whole range of checks, including security checks. There is one that I do to check the password strength of the SQL Server logins, based on this page on MSDN, PWDCOMPARE (Transact-SQL), except that I join to a huge list of all the common passwords that I can find that are known to the hacker community. I expire the password of any account that fails the check. It takes a minute or so, but it is very effective. I hope you have a similar system in place, even if you are already enforcing a windows password policy on SQL Server logins.

Why? Several times in the past, I've found SQL Server Logins with ridiculously weak passwords that have been assigned the SysAdmin role. In one case, it was the same password for that login on all machines in the domain. The test I run tells you what the password is, of course, but there are many ways to do that without leaving any forensic clues.

As a DBA and custodian of data, I'm motivated to be a saint rather than a sinner, of course, but I must admit that I enjoy checking that I indeed have access to all the other SQL Server instances in the domain at SysAdmin level. These privileges would give you control over every SQL Server instance, and if XP_CmdShell was enabled, then you could control the machine. Any determined and well-informed person would be able to make this permanent control. Your data is in the public domain and as a DBA you need to be very familiar with what it is possible for the sinner to do with a handy password, and how easily that sinner can get hold of it.

If you have a SQL Server that is exposed to the internet, even on just a couple of ports, you will probably find, as I have, that your intrusion-detection software will pick up countless probing attempts to log in via id/password. If you have a password that is weak, then the instance is at high risk. If the same password is used on several machines, then an intruder can leapfrog to the other machines, just as can be done from a SQL Injection attack.

If you really don't need SQL Server passwords, as when your instances are within the domain, then don't use them. Use Windows security instead.

Phil Factor

Phil Factor from SQLServerCentral.com

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

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Tech News : General Interest

Oracle CEO Ellison reveals most detail to date about 'multitenant' 12c database - The next version of Oracle's database will feature support for multitenancy as a critical feature, providing superior security, control, and efficiency for software services delivered from the cloud, CEO Larry Ellison said Sunday during the OpenWorld conference in San Francisco....(more)

Microsoft TypeScript: the JavaScript we need, or a solution looking for a problem? - For all JavaScript's prominence as the lingua franca of Web development, there are an awful lot of developers who don't like it a whole lot, and as a result, a great many efforts to produce something better......(more)

Signs of MySQL decline on horizon - While Oracle has continued to put effort into its free software MySQL database, there is a noticeable movement within the tech community to shift away from MySQL to other databases like PostgreSQL....(more)

Microsoft News : General Interest

The Story of the New Microsoft.com - A few weeks ago Microsoft silently launched a new home page. It was meant to be a temporary launch for the purposes of some preliminary testing. But as fate would have it, it became the talk of the Internet. ...(more)

Microsoft takes the wraps off TypeScript, a superset of JavaScript - Microsoft is launching a preview of a new programming language known as TypeScript, which aims to make JavaScript development scale beyond the client....(more)

Hardware News

State of the NAS: private clouds and an app platform - The current breed of NAS will share files with your tablet and host Wordpress....(more)

Blogs : Administration

Parent transaction ID in 2012 fn_dblog output - Why Paul; Randal is excited by the inclusion of the parent transaction ID in the output of fn_dblog, which allows us to see which transaction is the parent of nested system transactions and other sub-transactions....(more)

HOW TO: Find Currently Running Long SQL Agent Jobs - Thomas LaRock on how to find Agent jobs that are currently running, see how long they have been running for, and compare that to the historical average....(more)

Learn to Speak DBA Slang - Ever wonder what those big-company DBAs are saying when they start busting out the cryptic terms? Learn the slang of database administrators with this handy reference guide....(more)

Troubleshooting the Top Five Waits Occurring in SQL Server DBAs - We’ve all been there: the SQL Server Database Administrator has been running slowly and everyone’s getting cranky. How can you tell what’s wrong and what steps you need to take to restore normal operations?...(more)

Blogs : Analysis Services / BI

Tabular Models, Compatibility Level, and Power View - Dan English on why Compatibility Level is a pretty important setting for the users of our data models....(more)

Blogs : Computing in the Cloud

Inside the Complexity of Delivering Cloud Computing - On the planning and architecture required to achieve an infrastructure that offers the full benefits of Cloud computing. ...(more)

Blogs : Data Access / ORMs

Using SQL Server Metadata and Statistics to Build a Table Explorer Application - Windows Explorer displays, next to the file name, vital information such as file size, type, the date it was created and the contents last changed. From that you can assess at a glance the state of the list as a whole, without needing to examine the detailed properties and contents of individual files. In this article I'm going to argue that a similar idea could be usefully applied to database tables....(more)

Stored Procedures DO NOT increase performance - I assume that you have clicked on this article / blog because you are an awesome fan of stored procedures (like me) and you cannot see these kind of loose statements. ...(more)

Blogs : DMO/SMO/Powershell

Windows Process Affinity - Processor Affinity is another term for "what cores can my process use". By limiting a CPU hungry process to just use some of the available cores, it is possible to let other processes run more smoothly. This can easily be accomplished by right-clicking a process in TaskManager, but can it be done by a Powershell script?...(more)

SQL – Scripting out DB Mail Config with PowerShell and SMO - Phil Brammer on a script that overcomes a bug whereby the Mail.Script() output does not contain the SMTP server name and port number....(more)

HOW TO: Output STATISTICS IO Details Using Powershell - Tom LaRock on use of Powershell to automate a process that would connect to a database server and run a bunch of queries and return the results of SET STATISTICS IO for each one. ...(more)

Blogs : High Availability/Disaster Recovery

How To: Migrate from Failover Cluster Instances and Database Mirroring to SQL Server AlwaysOn – Part 3 - Part 3 of Cephas Lin's blog series that shows how to perform end-to-end HADR migration of SQL Server from a SQL Server Failover Clustering /database mirroring solution to AlwaysOn Failover Cluster Instances for high availability and AlwaysOn Availability Groups....(more)

Blogs : NOSQL

Load SQL Server BCP Data to Hive - As you start learning more about Hadoop you may want to take a look at how the same data and queries work for SQL Server and for Hadoop. There are various ways to do this. For now I’ll show you something that utilizes some of your existing SQL Server skills (BCP) and some new Hadoop skills (basic Hadoop FS and Hive commands)....(more)

Blogs : Performance and Tuning

Tools for Your Debugging Toolbox - A few favorite, and a couple of the lesser-known, tools for troubleshooting and degugging Windows software....(more)

Index Rebuild vs Reorganize: The transaction log edition - Comparing the log generations rates for a rebuild vs a reorg operation for a perfectly fragmented index...(more)

Scaling the Database: Data Types - Michelle Ufford starts a new series exploring the tuning and design changes required to support 27k transactions per second during the airing of Go Daddy’s Super Bowl commercials....(more)

SQL Server 2012 Diagnostic Information Queries (October 2012) - Glenn Berry's October 2012 version of his SQL Server 2012 Diagnostic Information Queries, with some minor tweaks and improvements to a number of the existing queries. ...(more)

Blogs : Professional Development

What Counts For a DBA: Simplicity - Too many computer processes do an apparently simple task in a bizarrely complex way. They remind me of this strip by one of my favorite artists: Rube Goldberg. In order to keep the boss from knowing one was late, a process is devised whereby the cuckoo clock kisses a live cuckoo bird, who then pulls a string, which triggers a hat flinging, which in turn lands on a rod that removes a typewriter cover…and so on....(more)

SQLBeat Podcast – Episode 5 – Kevin Kline on SQL, Professional Development and Book Writin’ - Kevin Kline, of SQL in a Nutshell fame, talks about Apple MacBooks (is that what they are called?), our beginnings in the industry, the Deep South, health care intiatives and 286's. ...(more)

My Favorite Suggestions for New Speakers - Some people are natural public speakers, others practice a lot and become very good. You need to become comfortable being uncomfortable, and you need to be patient. Don’t give up, have confidence, and above all, have fun....(more)

Blogs : Reporting Services

SQL Server Reporting Service Application 2012 DR Procedures - On finding out and testing the detailed steps to handle DR for a SSRS 2012 service application on SharePoint 2010....(more)

Blogs : Security and Auditing

Dealing with Orphaned Users - You just restored a production database on a development server. You’ve told the developers that it’s restored, and you start to relax in your chair. 30 milli-seconds later, your phone is ringing… the developers can’t connect to the database that you just restored. ...(more)

Filter SQL Server Audit on action_id / class_type predicate - In SQL Server 2012, Server Audit can be created with a predicate expression (refer to MSDN). This predicate expression is evaluated before audit events are written to the audit target. If the evaluation returns TRUE the event is written to the audit target else it's not. Hence one can filter audit records going to the audit target based on the predicate expression....(more)

Contained Databases inside SQL Server 2012 - Vinod Kumar on the concept of contained databases, a lesser-discussed feature of the SQL Server 2012 release....(more)

Blogs : T-SQL

SQL Server Management Studio: Basic Startup Options - SQL Server Management Studio is a powerful tool to manage SQL Server databases. This article provides a few options that can make our daily life easier. ...(more)

Sliding window scenario - when data spilled out to the right-most partition - Dmitri Korotkevitch on a problem where data spilled out to the right-most partition of the table and as result sliding window scenario did not work anymore....(more)

Articles

Different INSERT Methods Result in Different Page Density - Tim Ford on how using the GO some_number command after the INSERT statement in order to re-run the same line of code some_number of times. Ultimately I come to you now to tell you that this will result in different page storage behavior than other methods of inserting data into tables within SQL Server....(more)

Usage of dates argument in a row context - Several Time Intelligence functions in DAX use a "dates" argument, which has a special behavior because it can be expressed by using both a column reference syntax, a table expression or a Boolean expression. This article discusses how to correctly pass the "dates"argument in order to obtain the desired result also in complex expressions....(more)