SQL Server Best Practices from Microsoft

I was just reviewing a document from Microsoft and came across these SQL Server 2008 / 2005 Best Practices to be adopted during Data Connectivity, Authentication, Development and Query Writing etc..

  1. Use Windows Authentication to connect to SQL Server if your environment supports the use of Windows or Active Directory security credentials, otherwise use SQL Server authentication. If you use SQL Server Authentication, encrypt your connection strings to protect them.
  2. When constructing a connection string, always specify the Initial Catalog attribute to ensure your application is in the correct database context. Do not rely on default database settings.
  3. When accessing data in SQL Server, production applications should not use server-level principals that are members of the sysadmin server role (like sa), or database-level principles that are members of the db_owner role (like dbo). Designing a secure application requires the developer to employ the principle of least privilege. To implement this, create database roles that mirror the different types of users in your application, and grant only the privileges that are required to those roles.
  4. When accessing data in SQL Server, production applications should not use server-level principals that are members of the sysadmin server role (like sa), or database-level principles that are members of the db_owner role (like dbo). Designing a secure application requires the developer to employ the principle of least privilege. To implement this, create database roles that mirror the different types of users in your application, and grant only the privileges that are required to those roles.
  5. When opening connections in C#, do so in the scope of a using statement to ensure that the connection is properly disposed of. The failure to dispose of connections could result in running out of available connections and runtime errors.
  6. When writing a Transact-SQL SELECT statement, always constrain the results using a WHERE clause to avoid returning data to your application that will not be used. Tables can get very large, and processing result sets requires memory and CPU resources both in SQL Server and your application.
  7. When designing your database, make sure to create a primary key for each table in your database. A primary key is a single column or combination of columns that can be used to uniquely identify each row in your table. Primary keys are implemented using a PRIMARY KEY constraint defined when a table is created or when adding an index to your table. SQL Server’s query optimizer can produce more efficient execution plans on tables that have primary keys.
  8. When designing your database, consider creating an index on SQL Server columns that are frequently referenced in the WHERE clause of SELECT statements. This may yield a tremendous performance improvement depending upon the size of your table, the distribution of data in your column and the frequency of updates. The Database Engine Tuning Advisor tool can provide recomendations on which columns should be indexed and the types of indexes to create.
  9. Use of parameterized commands is recommended for security. Hackers can use an approach called SQL injection to insert malicious SQL statements into Transact-SQL commands. To guard against SQL injection, never directly execute text entered by a user as a Transact-SQL command. Instead, only use pre-defined Transact-SQL commands or stored procedures and substitute data values provided by the user using parameters.
  10. Use of parameterized commands is also recomended for performance. SQL Server uses a sophisticated cost-based query optimizer to determine the most efficient execution plan for commands at run time. Using parameterized queries helps SQL Server re-use these plans from execution to execution which can save query optmization CPU cycles.
  11. When retrieving only a single value from SQL Server, use ExecuteScalar.  This is more efficient than using the ExecuteReader.
  12. When designing your application, create a class to work with your data as a business object in ASP.NET. This will make it easier to leverage powerful web server controls that support data binding. Classes will also make it easier to work with your data in code because of Visual Studio features like IntelliSense and AutoComplete.
  13. Use the SQL Server IDENTITY property to generate unique identifiers when you want SQL Server to manage the creation of unqiue identifiers that are pneumonic.
  14. To improve the multi-user concurrency of your application, limit the scope and duration of transactions to short operations that do not require user intervention to complete. Avoid using distributed transactions except in situations where they are absolutely necessary. If you must use them, strictly limit their scope and duration. Distributed transactions can introduce additional latency and can be complicated to configure, monitor and debug.

Understanding SQL Server Connection String

To be placed within the XML File – Usually Web.Config

<connectionStrings>

<add name=”AdventureWorks” connectionString=”Data Source=SQLServerAlias;Integrated Security=True;Initial Catalog=AdventureWorks2008R2″/>

</connectionStrings>


SQL Server connection string on a basic level has a three different attributes.


1) Data Source

Normally the Data Source attribute is set to a string known as an instance, which in its simplest form is known as a default instance. A default instance simply specifies the name of the computer where SQL Server is installed, such as “MYSERVER”. You can also use “(local)” if the SQL Server resides on the same server as your application, a fully qualified domain name “myserver.mycompany.com”, or you can specify a TCP/IP address and port number.

Sometimes it is useful to have multiple SQL Server installations on the same server. To accomplish this, SQL Server setup supports the ability to install a single default instance and additional named instances. A named instance takes the form “MYSERVER\MYINSTANCE”. You can find out the names of all of the SQL Server instances installed using the SQL Server Configuration Manager tool.

In this lab, the Data Source is set to a server alias (‘SqlServerAlias’). A server alias provides a handy way to reference a SQL Server instance from multiple applications so it can be updated in one place without having to modify application source code. You create and edit a server alias using the SQL Server Configuration Manager tool.

2) Integrated Security

This attribute tells ADO.NET what type of authentication to use to connect to SQL Server. When set to True, ADO.NET will attempt to use Windows Authentication to connect to SQL Server. Windows Authentication makes it easy to use existing security accounts defined in Windows or Active Directory instead of creating duplicate security accounts in the database. An added benefit is that you do not have to include security credentials in your connection string.

In order to successfully connect to SQL Server using Windows Authentication, an administrator must have previously granted connection privileges to the Windows security account using the Transact-SQL CREATE LOGIN command or using a tool like SQL Server Management Studio. This is analogous to an administrator granting privileges to access other resources like a file, directory or share. SQL Server setup requires the user who is performing the installation to create a login for at least one Windows security account. Setup makes this login a member of the server-level sysadmin role.

When the Integrated Security attribute is set to False, ADO.NET will attempt to use SQL Server Authentication to connect. SQL Server Authentication is useful when you do not have access to Windows or Active Directory security credentials, or if Windows Authentication is not supported in your environment. SQL Server Authentication must be enabled on the instance you are trying to connect to. It also requires that you specify two additional attributes in your connection string: “User ID” and “Password”. If you have to use SQL Server authentication, encrypt your connection strings to protect them.

In order to successfully connect to SQL Server using SQL Server Authentication, an administrator must have previously granted connection privileges using the Transact-SQL CREATE LOGIN command or using a tool like SQL Server Management Studio. Here’s an example of a connection string that uses SQL Server authentication:

To be placed within the XML File – Usually Web.Config

<connectionStrings>

<add name=”AdventureWorks” connectionString=”Data Source=SQLServerAlias;Integrated Security=False;Initial Catalog= AdventureWorks2008R2;User Id=userName;Password=userPassword“/>

</connectionStrings>

3)   Initial Catalog

Let us move on and talk about the Initial Catalog attribute. SQL Server not only supports multiple instances per server, but it also supports multiple databases per instance. This lab uses a database named “AdventureWorks2008R2”. Specifying an Initial Catalog in the connection string automatically switches the context of the current database when the application connects to ensure you are accessing the correct data, so it is important that you set it correctly to avoid making changes in the wrong database (like the master system database for example).

Because each database may have different security and access requirements, SQL Server supports a robust security architecture based upon security principals. There are three layers of principals, Windows-level principals, SQL Server-level principals and database-level principals, and each of them can be secured separately. So far we have only dealt with access control for Windows-level and SQL Server-level principals. These principals will get you connected to SQL Server, but you will need to understand and properly configure database-level principals to actually work with data.

The most common database-level principle is known as a database user. Database users are created in specific databases using the Transact-SQL CREATE USER command or a tool like SQL Server Management Studio. Each database user is associated with a server-level principal. In order to access or modify data, the database user performing the operation must have been granted privileges to do so using the Transact-SQL GRANT command or a tool like SQL Server Management Studio. Those privileges can be granted directly to the database user or to a database role that the database user is a member of. Examples of privileges include SELECT, UPDATE, INSERT, DELETE and EXECUTE (for running stored procedures).

Note that some server-level principles, such as those that belong to the server-level sysadmin role, are automatically associated with a high-privileged database user known as dbo. This is a special user in all SQL Server databases known as the “database owner”. The database owner can perform all actions in a particular database because it is a member of the db_owner database-level role. Since this lab assumes your Windows security account is a member of sysadmin, we are also assuming you can access the AdventureWorks2008R2 database and perform operations in it as the database owner.

Developers have the option of configuring their applications to connect to the database using the security credentials of the user, or alternatively using security credentials associated with the application (which in ASP.NET is known as an application identity). Using the user’s security credentials provides more flexibility in controlling access to data using built-in SQL Server functionality in an application-independent fashion, while using an application identity requires you to implement most data access control yourself in application logic, which is fine if only one application will access the data.

Reference: – SQL Server 2008 R2 Training Kit

IIS – Secure your Web Server from Attacks, Denial of Service (Dynamic IP Restrictions)

http://www.iis.net/expand/DynamicIPRestrictions

Dynamic IP Restrictions an Extension for IIS 7.0 and IIS 6.0 available for download from the above link. It allows you to secure your site from Brute-force, Denial of Service attacks, by temporarily blocking Internet Protocol (IP) addresses of HTTP clients who follow a pattern that could be conducive to one of such attacks. This module can be configured such that the analysis and blocking could be done at the Web Server or the Web Site level.

Dynamic IP Restrictions for IIS also maintains support for the functionality already provided by IPv4 Address and Domain Restrictions in IIS 7.0, thus allowing IT Administrators to build and use a static list of IP addresses and Domain Names that are denied or granted access.

So You can download and it will seamlessly integrate with IIS 7.0 and will provide you much comfort with added security!

Important Facebook Safety & Security Tips

Facebook Security Tips & Techniques

After analyzing Facebook Security, we came up with some important Facebook Security tips and measures which an account holder needs to take in order to ensure the maximum protection of their Facebook Account.

1)Avoid installing Applications from the Publishers & Developers you don’t trust. Like it’s pretty ok to play farmville, Cities I have visited and others. But don’t install every app targeting segments which are un-realistic like who viewed your Facebook profile etc. If you are unaware of the Apps credibility go and checkout the company’s or developer’s website who have developed it. If it is unavailable and if you’re not sure about the credibility so then don’t install it. If you install and unknown app and have agreed to provide your data to it (which is necessary most of the times) they can view all information present under your Facebook profile and can share your posts and pics with anyone (probably paying them for this)

2)Always use strong passwords, strong security questions, keep changing passwords and your Facebook password should not be the same of your home, office, school’s PC or any Email accounts and other services. Never respond to the offers (free laptops, free gifts) and don’t provide your private info by replying to any person/thread claiming that they are from Facebook and require this in order to continue the service.

3)Monitor Your Facebook Privacy Settings and adjust them as per your requirement.
http://www.facebook.com/settings/?tab=privacy#!/settings/?tab=privacy

Adjust Profile Privacy Settings, who can view my Photos, Videos, Personal Information and other feeds

facebook information privacy and security

What my friends can share about me through Applications and Websites

facebook apps privacy

Happy Facebooking!!

Facebook plans to add ‘place’ feature

Facebook proposed changes to its privacy policy that include a new "places" feature.

Facebook proposed changes to its privacy policy that include a new “places” feature.

Facebook on Friday proposed creating a way for people to add their locations to Facebook pages but released almost no details about how the feature will work.

The location-based feature, hinted at in a post on Facebook’s blog, would give the social network’s 400 million-plus members a function that has been popularized on newer “location-based” Web sites like Foursquare and Gowalla.

Those two sites feature mobile apps that are set up like games, encouraging smartphone- or laptop-wielding users to “check in” at restaurants, businesses and public locations. When a person checks in to a certain spot, his or her friends are alerted about their whereabouts.

Posting locations in addition to status messages and Web links has become a major theme of online social networking this year.

In a blog post on Friday, Michael Richter, Facebook’s deputy general counsel, provided few details about how the places feature would work but did confirm that Facebook is working on features that use people’s locations.

He writes that the addition is “more exciting” than a location feature the company had been planning.

“The last time we updated the Privacy Policy, we included language describing a location feature we might build in the future. At that point, we thought the primary use would be to ‘add a location to something you post.’ Now, we’ve got some different ideas that we think are even more exciting,” Richter wrote.

“So, we’ve removed the old language and, instead added the concept of a ‘place’ that could refer to a Page, such as one for a local restaurant. As we finalize the product, we look forward to providing more details, including new privacy controls,” the post says.

This month, The New York Times cited unnamed sources saying Facebook would unveil its location-based feature at its annual f8 conference for Facebook application developers, which begins April 21 in San Francisco, California.

Friday’s Facebook post also says that the massive social networking site will make changes “sometime soon” to the policies that govern how it works.

Such alterations typically draw raucous debate, and often backlash, from the Facebook community, but the site says it is announcing the changes for review before they go into effect so users will have time to read them and post comments.

The idea of a “place” is mentioned at least twice in the proposed policy.

“Once you register you can provide other information about yourself by connecting with, for example, your current city, hometown, family, relationships, networks, activities, interests, and places,” the policy says.

In a section about information the site collects about you from other users, the policy says: “We may collect information about you from other Facebook users, such as when a friend tags you in a photo, video, or place, provides friend details, or indicates a relationship with you.”

The addition of “place” into Facebook lingo is an important change, Marshall Kirkpatrick writes on the blog ReadWriteWeb.

“The difference between location and Place is a significant one. Substantial resources are dedicated by location-aware social networks to determine what ‘place’ your location refers to,” he writes.

“That might mean neighborhood, it might mean business name and it might mean recognizing when you are posting from home so that location can be selectively hidden if you so choose.”

On the tech blog VentureBeat, Kim-Mai Cutler writes that the “place” feature could make Facebook pages for businesses and television shows more interesting.

“This could make Fan Pages for restaurants a lot richer and maybe even competitive to Google’s Place Pages or Yelp’s listings,” she writes.

“If you could tag an update or post with a venue, you probably attach comments, mini-reviews and photos to the Fan Page.”

Essential BlackBerry tips

Wipe It!

Replacing your BlackBerry smartphone is like selling a used car – you need to make sure you clear it out first! Here are three easy ways to do that:

  1. Wipe your BlackBerry smartphone data.
  2. If you have a BlackBerry® Internet Service account, contact your carrier and ask them to remove it. If you don’t, your emails will keep being sent to your old smartphone even after you wipe it.
  3. If you have a corporate BlackBerry smartphone, ask your IT admins to remove the IT policies. The new owner will thank you!

Protect It!

Of course, smartphones are also easy to lose, and many are stolen each day. To protect your personal information in case your BlackBerry smartphone is lost or stolen:

  1. Make sure you set a password. By default, an attacker only has 10 tries to get it right, so making it hard to guess is very important. Here some useful tips on choosing a strong password.
  2. Use content protection to encrypt all of your personal data. You can enable this by selecting Options -> Security Options -> General Settings on most BlackBerry smartphones.