SQL Server security best practice

Security! This is the word comes in mind of every concerned person when it come...

Change the Collation Settings in MS SQL Server

This post will show you how to change the collation settings in MS SQL Server for specific database...

Resolve collation conflict

In this post I will show you how you can resolve collation conflict error...

Book: SQL Server 2008 High Availability

In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring...

Why to recompile Stored Procedure

Generally, we create views and stored procedures (proc here after) ...

11/09/2013

SSAS– Resolving target machine actively refused

medium_5462764117

I always wanted to have my hands on SSAS, SSIS and SSRS as I always feel short-hand in these areas. Yesterday I decided to get my hands dirty with one of this – SSAS.  Few month back I have created a virtual lab on my 4 year old Dell Laptop with 3 VMs and about total 4 instances of SQL Server – 2 on Host machine and 2 on Guest, one of them is having SSAS installed on one of the named instance.

With lot of excitement I tried connecting to SSAS – it was unsuccessful attempt!! It shows me an error message which says - A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)

SSAS_Error

As we always do – Googled and it lead me to the connect item filed by Aaron Bertrand ( Blog | Twitter ). Reading through the post I came to know that if I try to connect to the named instance SSAS it’s chance that I get this very error. I also have the similar case, I have renamed my machine hence this error showed up with the named instance.

Workaround:  As suggested in the comment section, I have granted NTFS permission on folder program files (x86)\microsoft sql server\90\shared\asconfig for the SQL Browser service account and I am sorted!!

Reference: Microsoft Connect ID 679792 

photo credit: JLaw45 via photopin cc

—Hemantgiri S. Goswami (http://www.sql-server-citation.com)

11/03/2013

SQL Server–Security Risk Assessment PMP Way

clip_image001Recently I had a chance to speak my friend Ashish Sharma who is my colleague too. We were discussing Project Management topics (he recently passed PMP and ACP). Our discussion ended at very interesting topic – RISK Assessment.
Definition: According to PMBOK, Projects can have unforeseen event or activity that can impact the project’s progress, result or an outcome in negative or positive way. Further, A Risk can be assessed using two factors – impact and probability. Determining the quantitative or qualitative value of risk related to a concrete situation and recognized threats is known as Risk Assessment.
So, coming to the point, how do we do Risk Assessment for Database systems, especially SQL Server ?
I myself have never come to situation where I have created or used Risk Assessment Register for SQL Server. I was thinking is there some readily available Risk Assessment Register / document somewhere? Hence, I have decided to research little more on it. I found absolutely nothing on this very topic. However, searching for different term - SQL Server security, and Risk Assessment – none found.
After spending some time researching I have found a good sample document for Risk Assessment Register that we can use, you can download it from here.
Now, this is what we have is Risk Register, how about threats? Sometime back, in 2009 I have wrote an article on SQL Server Security, using that article I have tried to pen down some threats that I can think off at this moment, they are:
Possible Threats Risk Mitigation
Virus Attack Install AV on DB Server however do not forget to exclude SQL server files
Unauthorised Logins Always use Audit (C2, Windows, SQL or both) failed login attempts and analyze them regularly. Always, rename SA / Admin account and use strong password mechanism for all the servers. If possible always try to use domain authenticated acconts.
MiM Attack To metigate MiM attach do following :
  • Isolate database server from Application / IIS Server
  • Configure SQL Server to use non-default port
  • Use Encryption / SSL
  • Use Firewall inbound / outbound rules
Root Access To avoid someone getting root access do following:
  • Disable / Rename in-built Admin account
  • Grant least access to Service Account
  • Use strong / random password
SQL Injection Do following:
  • Avoid using Dynamic SQL when you can
  • use parameterized query / stored procedure 3) Always validate the value at form level before it's being passed to database server
  • Refer above items
Known Configuration Always try to customize default configuration if you can, for example:
  • Run SQL server on non standard port
  • disable / rename SA account
  • Disable xp_cmdshell and other extended procedures

References :
http://en.wikipedia.org/wiki/Risk_assessment
http://en.wikipedia.org/wiki/Project_risk_management
http://www.brighthubpm.com/risk-management/3247-creating-a-risk-register-a-free-excel-template/
photo credit: kenteegardin via photopin cc
-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )