Posts Tagged ‘Management’

SQL Server 2012: User-defined Audit April 2nd, 2013

Vinod Kumar

Coming back from TechEd 2013, I have loads to share. I have done a number of sessions and I am glad I could meet all of you at the event. This blog gets inspired from what was discussed during the event. One of the lesser known features introduced with SQL Server 2012 is the ability to have User-defined Audits inside SQL Server.

Let me start by creating the normal steps of creation of Audits. Click Security –> Audits –> Right Click “New Audit”.


Here we are configuring the “WHERE” part of Audit. Once clicking the OK, make sure to right click and Enable the Audit.

The next section involves the “WHAT” are we going to audit. Click Security –> Server Audit Specification –> Right Click “New Server Audit Specification …”.


Give it a Name and select the Audit we created in the previous step. Under Audit Action Type, select “USER_DEFINED_AUDIT_GROUP”. This is the new Action type introduced with SQL Server 2012. Click OK to complete the step. Donot forget to Right Click and Enable this Server Audit Specification.

Now that we have created both where we are going to audit and what we are going to audit. The next step is to create the audit.


SQL Server 2012 introduces the new stored proc “sys.sp_audit_write” and this invokes a note on our Audit.

EXEC sys.sp_audit_write 1, 1, N’1 – This is additional Information’

Above is the command for your reference. The second parameter can be used for success or failure. 0=Failure while 1=Success – feel free to use it accordingly.

Once the above statements are fired, we can view the same on our Audit file. Select Security –> Audit –> UserDefinedAudit –> Right Click “View Audit Logs”. We will see 3 records. One to start the service and the two based on our commands.


In this log we can see the command statement, Succeeded as False, and User Defined Information contains the text we wrote. So this is complete in all sense.

Finally, I during the demo I also showed how we can read the Audit files located in a location using TSQL command sys.fn_get_audit_file command. All the UI details are also available via this TSQL too.


Hope you found this interesting and do tell me if you will be using the same in your environments.

Continue reading...


Business Secrets we learn everyday March 22nd, 2013

Vinod Kumar

My role involves meeting multiple customers and this it is so much fun to learn from so many of you. In this blog post let me pen down some of the awesome learning I have made over the years on what successful businesses and successful people have taught me. This learning can never be enough and I look forward to the same learning experiences in the years ahead too. So hope you will love this post and do let me know what did you learn from this post.

Here are my top 25 things I have learnt from my customers during lunch times:

  1. Businesses are about making people happy, not about just making making money.
  2. Businesses wins hearts first being honest, the business may not come in the short term but eventually come later.
  3. Only way customers will care for you is when you care for them first.
  4. Strategies help you build your runway, execution helps you take off.
  5. Motivating people is easier when you believe in yourself and are self motivated.
  6. You save more when you spend it at the right time.
  7. Just because social media is free doesn’t mean we will get constant flow of results.
  8. You passion must speak in whatever you do. Do it with conviction.
  9. More than competing, cooperating helps grow your business.
  10. You might be quitting when you are nearing success. So keep going till the end.
  11. The owner doesn’t pay your paychecks, it is the customer who pays for you.
  12. Always keep the a room free – the room for making process / product improvements.
  13. Social Networking can help you boost your business but you must create a personality to drive the same.
  14. Know to say Thank You and Apologies without apprehensions. These can be a great way to start a conversation.
  15. Discover the happiness within you if you plan to making customers happy.
  16. Constantly network and open your wings to discipline yourself in this.
  17. Businesses must have the willingness to learn and adapt accordingly.
  18. You might face failures instantly, but success comes gradually. You need to keep chipping away slowly.
  19. Let your product / services speak more than you.
  20. Think big, think globally. Act locally.
  21. Value the little things and give importance to less important people too. There will always be times when you will need them.
  22. Work harder, plan accordingly and once you made up your mind. Never consider the possibility of failure.
  23. Don’t fall for data. Sometimes data doesn’t tell you if you are doing the right things.
  24. Be professional while doing business.
  25. << Let me leave this space to learn from you >>

The loads of lunch table conversations have taught me so much that I found all these to share. I am sure you would have learnt some from your leaders in your respective companies. Do feel free to share with me the same. Would love to learn the same from you.

Continue reading...


Picture Post: Partitioning our time March 14th, 2013

Vinod Kumar

Time is what we want most, but what we use worst – William Penn

It is sometimes interesting to see how we dismiss with the fact that –“We don’t have time”. Time is relative and I keep telling people that every one has the same 24 hours. It is upon us to make sure how we make use of this time. We have different perspective on time utilization. Here is my view of taking your time and slicing it against 4 quadrants. This is my humble shot at how we box our time management.

A great leader’s courage to fulfill his vision comes from passion, not position – John Maxwell

Passion, though a bad regulator, is a powerful spring — Ralph Waldo Emerson

Does this make sense? Where do you think you are spending most of your time. Is it Top left (1), Top right (2), bottom left (3) or in the bottom right (4) quadrant? I have interestingly seen people get to quadrant 4 and in couple of years feel they have actually selected quadrant 2. What are your thoughts? Do drop me a line and would love to hear your perspective.

PS: For a change thought to make the picture really colorful, hope that is ok ;)

Want and boredom are indeed the twin poles of human life.

Continue reading...


TechEd India 2013: SQL Server and Architecture March 13th, 2013

Vinod Kumar


This time of the year that I get heads-down to bring some cool demo for TechEd and the exploration never stops. This year TechEd India 2013 happens in two cities – Bangalore and Pune. This time next week the Bangalore event would have got over and I am sure we will be in exhausted state to relive the past two days and staring at Pune to execute the same. There is always extra care, attention and preparation that goes behind for every TechEd. This year is no different. I am also excited because this will mark my 10th Year of TechEd on stage. The memories of doing this in 2003 is still fresh and the excitement never dies every single year.

The twitter hashtag to follow for this year TechEd is: #TechEdIn.

What is special about TechEd?

If you ask me there are a number of reasons to be at TechEd personally. My drivers for you will be:

  1. It is a place to socialize and at the same time feel alone. If you attend the tech sessions, you might feel there is so much more to learn and this sometimes alienates people in a good way. Get motivated and get to see what the future of technology is.
  2. Two days of pure learning and no work is something we will never get :). I know it is not a nice thing to say but I couldn’t resist from mentioning. Let me reiterate, it is not vacation time but learning time.
  3. It is a hub for networking. This is one of the key and most important reason to attend. Meet peers, speakers from Redmond, local speakers and many more.
  4. With close to 6+ tracks, HOLs – the choice of sessions are too many. It is always fun !!!
  5. It is also a place to make business contacts, find mentors, make new friends and all these will help you to make your future.

SQL Server – Security Session

For the past two years I have talked around Security in SQL Server. Each year I try to bring some unique perspective and what I find useful from a security point of view. This year it will be new additions to Security with SQL Server 2012. You will get to see:

  1. What has changed in Security
  2. How to use User Defined Roles and Default schema
  3. How to keep DBA out of database (access)
  4. Enhancements to Auditing
  5. Audit filtering and Audit resilience
  6. Contained Databases basics
  7. Using Logon Triggers with Contained Databases
  8. SQL Server 2012 – Service account changes
  9. and if time permits few more demo’s are available …

So don’t miss the action. I have infact given away what to expect out of my 1 hour session. Lots of demo and lots of detailing on how to use SQL Server security effectively.

SQL Server – Transaction Logs Session

Going back to the basics is important. So every TechEd there is one topic that I talk about which is about going back to basics. One question that I get back from customers almost every other session is around Transaction Logs functioning. I think it will be great to have a session around that.

  1. What is Transaction Logs? Why do we need them?
  2. Understanding what VLF’s are
  3. Creation of Logs and the basics
  4. Cyclic Logs and how they function
  5. What is written to Logs and how are they written
  6. What happens when Page Split happens with Logs
  7. How does minimal Logging work and what should we know
  8. Finally if time permits, how does recovery work …

This is just a teaser to what is in store for the session. We have many more interesting concepts to discuss and learn during the session – so don’t miss out.

Architecture Track – Evaluating Availability Options

This is going to be an interesting topic. As part of MTC I discuss about scale out to almost every single customer. Most of the times the discussion boils down to when to use what. We have solutions from Log shipping, Mirroring, Clustering, Replication and now with SQL Server 2012 – AlwaysOn. So the session is around:

  1. Why Scale out? Cant I just scale up?
  2. What changes do I need to do in my application?
  3. When to use what technology – Pre SQL 2012
  4. With SQL 2012 – What is AlwaysOn
  5. What are the 12+ different ways to deploy AlwaysOn for my environment?
  6. How do I decide and what will be the RPO and RTO for each?

This is a pure whiteboarding diagram based discussion. You will learn the theory and the possibilities when working with SQL Server 2012.


As I said, if you are in Bangalore or Pune – don’t forget to attend my session. Most importantly don’t forget to pass me a “Hi” after the session. I will be walking around and it will always great to make a new friend.

PS: If you are in Bangalore or Pune – the Usergroups have given some amazing discounts that you don’t want to miss. It is still not late, check out the Bangalore .NET and SQL Usergroup announcements.

Continue reading...


SQL Server: Security Practices and Checklist March 8th, 2013

Vinod Kumar

Now that you did get a chance to see my previous post around Database designs there have been few who have asked more of these posts. In continuation to that concept, this blog post will concentrate on highlighting the SQL Server security practices that I often call out and recommend to customers in our Architecture and design sessions. As always the disclaimer remains the same – these are generic practices / suggestions and might not be the same in your environment and deployment scenarios.

Security is a topic that is always seen as a after thought and I start reiterating to our every single customer how critical and important this is. Most of these are to be done by the database administrator of the environment. It is also left to us to keep track and constantly audit if any of them have been deviated or broken.

Other Practices posts have been:

SQL Server: Database Design Practices

This by no means is not the end-all list of security tips I suggest. But this is a great starting point for you.

General Suggestions

  1. The SQL Server service pack level is current.
  2. Ensure physical security of your server and only authorized people get access to the datacenter.
  3. Make sure SQL is installed with Windows Authentication as much as possible.
  4. Cross database ownership chaining is disabled.
  5. Always block TCP port 1433 and UDP port 1434 on your perimeter firewall. If named instances are listening on additional ports, block those too.
  6. Database Engine Ad Hoc distributed queries is disabled.
  7. Make sure xp_cmdshell is disabled. Don’t use it.
  8. The default trace option is enabled by default. Though non-intrusive, check the need if the same needs to be on production environment.
  9. The DB software installation account is restricted to authorized users only.
  10. Automated tools are used to provide audit trail reports using System Center or any other tool.
  11. SQL Server Agent jobs are monitored in accordance with procedures for failures and success.
  12. Database data files are stored in the same logical storage partition and permissions are proper.
  13. Never install SQL Server on a domain controller.
  14. The ports which the DB uses should be configured appropriately and must not be left to default (1433).
  15. SQL Server registry keys should be properly secured.
  16. Plan for an upgrade/migration plan to address an unsupported DB software version.
  17. Remove sample databases from production servers.

Logins Related

  1. Make sure DB password complexity has not been disabled.
  2. DB account passwords are set to expire every 60 days or more frequently based on your environment.
  3. The sa password has been changed from the default. Disable "sa" and make sure to rename them.
  4. Make sure the ‘remote admin connections’ (also referred to as the Dedicated Administrator Connection (DAC)) option is not disabled.
  5. Dedicated accounts have to be designated for SQL Server Agent proxies.
  6. The Database installation account is separate from the logged in user on the server.
  7. Do review changes to DBA role assignments and the logins who have DBA role access.
  8. Make sure no unnecessary privileges granted to the Windows Service accounts or to Windows Users on the local machine.
  9. The SQL Server service uses a least-privileged local or domain user account.
  10. The SQL Server service account is not assigned excess host system privileges.

Audit Related

  1. Audit trail data is not maintained for one year.
  2. The auditing of logins is configured for failed (or all) logins.
  3. Database executable and configuration files are being monitored for unauthorized modifications.
  4. Transaction logs have to be reviewed for unauthorized modification of classified data.
  5. Audit trail data is reviewed regularly to detect database access by unauthorized applications.
  6. Audit records are restricted only to authorized individuals.
  7. Make sure audit logs are included in backup operations regularly.
  8. Auditing attempts to bypass access controls must be logged.
  9. Trace Rollover should be enabled for audit traces that have a maximum trace file size.

Encryption related

  1. Database passwords used by batch and/or job processes should be in encrypted format.
  2. Sensitive information stored in the database has been identified and protected using encryption.
  3. The Service Master Key is backed up and stored offline and off site.
  4. Make sure the procedures and restrictions for import of production data to development databases are implemented. If obfuscation of data needs to be done while importing, this needs to be adhered to.
  5. Access to Master Key is restricted, controlled and audited.
  6. Symmetric keys should use a master key, certificate, or asymmetric key to encrypt the key.

Permission related

  1. Permissions assigned to PUBLIC or GUEST for a database must be reviewed monthly.
  2. Application user privilege has to be reviewed monthly or more frequently to ensure compliance with least privilege and documented policy of your organization.
  3. Track who has permissions in using the WITH GRANT OPTION.
  4. Never grant permissions to the public database role.
  5. Do not enable the guest account.
  6. Periodically scan fixed server and database roles to ensure that membership is only granted to trusted individuals.
  7. Ensure that the mapping between database users and logins at the server level is correct.
  8. Assign permissions to roles rather than directly to users.
  9. Objects may be owned by roles / schemas, rather than directly by users, if you want to avoid application changes when the owning user is dropped.

Common Security Lapses

  1. DB backup and restoration files are not protected from unauthorized access.
  2. Make sure the Database Master Key passwords are not stored in credentials within the database.
  3. Database backup procedures are defined and implemented properly.
  4. Backup and recover procedures have been implemented/tested.
  5. Check for unapproved inactive or expired database accounts on the database/server.
  6. The DB is periodically tested for vulnerability management and compliance.
  7. Make sure the database is NOT accessible to internet users and is located in a DMZ.
  8. Replication snapshot folders are protected from unauthorized access.
    Linked server providers should not allow ad hoc access.

Miscellaneous Points

  1. Developers are not assigned excess privileges on production databases.
  2. The Named Pipes network protocol is disabled.
  3. The Analysis Services ad hoc data mining queries configuration option has been disabled.
  4. Analysis Services Anonymous Connections are disabled.
  5. Track and audit stored procedures executing at startup.
  6. Error log retention should be set to meet log retention policy. Else increase number of Log files.
  7. Database TRUSTWORTHY status should be authorized and documented or set to off.
  8. The SMO and DMO SPs option should be set to disabled if not required. Try not to use this.
  9. The Agent XPs option should be set to disabled if not required.
  10. Database Engine Ad Hoc distributed queries should be disabled.
  11. Run separate SQL Server services under separate Windows accounts.
  12. Assign static ports to named instances of SQL Server.
  13. Keep an inventory of all versions, editions, and languages of SQL Server for which you are responsible. Include instances of Express Editions in your inventory as it will help in patching.

Do tell me if you found this useful and I am sure there are areas of Development and coding that I will write in future blog posts.

Continue reading...