Friday, September 30, 2011

Creating Secure SQL Server Service Accounts

Problem

We are looking to install SQL Server and we know it will require a service account. In this tip we cover what should be done to configure the SQL Server service accounts securely.

Solution

Service Accounts for a Server Installation

If you're on a domain, it's generally recommended that you use a domain level account. This should be a regular domain user account and definitely not a member of the Domain Admins group. The reason for the domain user account recommendation and not a local account is that it allows Active Directory to be the single source for your security system. It makes things easier to manage and audit. If you don't have a domain, using a local account is fine, just don't make it a member of the Administrators group (if you go the domain user account route, you should also avoid this group membership).
If you're installing multiple components, the recommendation is to use separate accounts if you can. If you're just installing the database server, you have two services to be concerned with: the main SQL Server service and the SQL Server Agent service. Even in this case it's recommended to use separate accounts. While this is a best practice, it is not unusual to see a single account per server for all of the SQL Server services. However, the issue here is those additional services could potentially have permissions in other SQL Server components you might not want because they aren't needed. For instance, the SSIS service doesn't need to be able to control the database engine. However, if you use the same account, there's a potential for that to happen.

As far as assigning specific permissions, don't. Let the installer set the permissions correctly. It will attempt to set the minimum permissions necessary. If at some later point you need to change the service account, use the SQL Server Configuration Manager because it will handle the permissions as well as a few other details that are not done by changing the service account using any other interface.

One definite no-no is to re-use a service account across servers unless you're dealing with a situation like a scaled-out SQL Server Reporting Services installation. For the database engine, SQL Agent, and SSIS, you should ensure that no service account is used on multiple servers. If you have the need for a service account to access resources on a second server, you can always assign the permissions explicitly.

Considering SQL Server 2008 and Windows Server 2008 and above

If you have the combination of SQL Server 2008 or above and Windows Server 2008 or above, then SQL Server will be installed using an operating system feature called Service Isolation. In terms of user accounts, all user accounts have a unique identifier we call a SID (Security Identifier). With Windows Server 2008, services also have SIDs. This was implement for the situations were you have multiple services running under the same account (for instance, Network Service or System) but these services shouldn't have access to resources belonging to other services that happen to share the same service account. As a result, each service now has a SID.

SQL Server 2008 and above can use this to ensure that the service has permission to database resources, not the account. For instance, in the figure below, note that NT Service\MSSQL$SQL2008R2 (a SQL Server 2008R2 named instance called SQL2008R2) and its SQL Agent service, NT

Service\SQLAgent$SQL2008R2, are listed as logins. This is because of service isolation. In this case, the only way you get sysadmin level access is by coming through those services. Even if the service account was to be compromised, unless it was one of those services connecting, the account would have no privileges within SQL Server.

Handling Developer Workstations

When considering developer workstations, there is no change in the recommended best practices, but their probably ought to be. If your organization has a lot of developers, then keeping up with services accounts for ever developer installation can be a management nightmare. In most cases developer workstations shouldn't have processes that need to access anywhere outside of the workstation SQL Server is installed on. In this case it makes more sense to create local user accounts for the various SQL Server services. The same rules apply: separate accounts for different services and starting each user account as a user level account and allowing SQL Server to update the permissions accordingly.

One thing you don't want is for the developers to use their own accounts. This can cause a problem at password reset time. A classic scenario is the developer gets the message to change the password when he or she logs on to the domain and follows suit. However, the SQL Server service would still be configured to run with the old password. As a result, next time SQL Server is started, you'll have an audit failure. This is true for all accounts with that same user account but now the problem is every single one of those services will attempt to log on to the domain as if nothing has happened. The problem then is each of those services will generate audit failure events and could potentially cause the user account to be locked out, depending on the lockout policy. Obviously this is a situation to be avoided.

Also, if you still have developers on XP, it's time to migrate. The Service Isolation spoken of earlier is first available in Windows Vista. So a combination of Windows Vista or higher and SQL Server 2008 or higher will ensure you get Service Isolation and minimize your exposure.

In Summary

  • For servers use domain accounts
  • For developer workstations use local user accounts
  • Never configure the accounts to be anything more than regular user accounts. Let SQL Server make the permission changes.
  • Use separate accounts for each service.
  • Never re-use an account on multiple servers, with the exception of scale out scenarios like is possible with SQL Server Reporting Services.
  • Try to use Windows 2008 or higher for the operating system

Friday, September 23, 2011

SQL #

SQL# is your QUICKEST and EASIEST way to extending the power of T-SQL in Microsoft SQL Server 2005 and newer!

You can be more productive in just three easy steps:
1) Download the SQL# script
2) Run the SQL# script
3) Use SQL# functions and procedures

The following are the free functions available with SQL# now:

Current FREE functions:

  • String: Contains, Count, Cut, EndsWith, Equals, IndexOf, InitCap, IsNumeric, Join, LastIndexOf, Newline, NthIndexOf, PadLeft, PadRight, Replace, Split, SplitIntoFields, StartsWith, Trim, WordWrap

  • RegEx: CaptureGroup, Escape, Index, IsMatch, Matches, Match, MatchLength, MatchSimple, Replace, Split, Unescape

  • Math: CompoundAmortizationSchedule, Constant (30 physics constants), Convert (22 measurement conversions), Cosh, CubeRoot, Factorial, IsPrime, RandomRange, Sinh, Tanh, Truncate

  • Date: Age, BusinessDays, DaysInMonth, DaysLeftInYear, Extract, FirstDayOfMonth, FormatTimeSpan, FromUNIXTime, FullDateString, FullTimeString, GetDateTimeFromIntVals, GetIntDate, GetIntTime, IsBusinessDay, IsLeapYear, LastDayOfMonth, NewDateTime, NthOccurrenceOfWeekday, ToUNIXTime, Truncate

  • InterNet: AddressToNumber, HTMLDecode, HTMLEncode, IsValidIPAddress, NumberToAddress, URIDecode, URIEncode, URIEncodeData, URIGetInfo, URIGetLeftPart

  • Miscellaneous: CRC32, Deflate, GenerateDateTimeRange, GenerateDateTimes, GenerateFloatRange, GenerateFloats, GenerateIntRange, GenerateInts, GetDirectoryName, GetFileName, GetRootDirectory, GUnzip, GZip, Hash, HashBinary, Inflate, IsValidCC, IsValidCheckRoutingNumber, IsValidConvert, IsValidPostalCode, IsValidSSN, ToWords (i.e. translate a number into word representation: 150 = One Hundred and Fifty)

  • Database: BulkCopy, BulkExport, ForEach (combined ForEachDB and ForEachTable), HTMLExport, XOR

  • Convert: BinaryToHexString, DateTimeToMSIntDate, FromBase64, HexStringToBinary, HtmlToXml, MSIntDateToDateTime, ROT13, ToBase64, UUDecode, UUEncode

  • LookUps: GetCountryInfo (i.e. ISO info), GetStateInfo (i.e. ISO info)

  • Internal: Version, Help, Setup, Uninstall, GrantPermissions, IsUpdateAvailable, SetSecurity, WebSite

  • Operating System: EventLogRead, EventLogWrite, GenerateTone, MachineName, Uptime

  • Twitter: BlockUser, CreateFavorite, DestroyDirectMessage, DestroyFavorite, DestroyStatus, FollowUser, GetBlocks, GetFavorites, GetFollowers, GetFriends, GetFriendsTimeline, GetHomeTimeline, GetMentions, GetMessages, GetPublicTimeline, GetRetweetedBy, GetRetweetedByMe, GetRetweetedToMe, GetRetweets, GetRetweetsOfMe, GetSentMessages, GetStatus, GetUser, GetUserTimeline, Retweet, SendDirectMessage, UnBlockUser, UnFollowUser, Update, xAuth

  • User-Defined Aggregates: GeometricAvg, Join, Median, Random, RootMeanSqr

  • User-Defined Types: FloatArray, HashTable, NVarcharArray
Download  the PDF manual

Friday, September 16, 2011

SEQUENCE in SQL Server 2011

SQL Server 2011 (or Denali) has now arrived CTP. In this article we will look at a core new feature of SQL Server 2011 which is SEQUENCE. If you are familiar with Oracle, you will already know all about this feature since it has been standard on Oracle more than 10 years I believe.

What is Sequence in SQL Server ?

In simple terms, it is a new database object and a substitute for the Identity of columns.
Using the identity attribute for a column, you can easily generate auto-incrementing numbers (which as often used as a primary key). With Sequence, it will be a different object which you can attach to a table column while inserting. Unlike identity, the next number for the column value will be retrieved from memory rather than from the disk – this makes Sequence significantly faster than Identity. We will see this in coming examples.

Creating a Sequence in SQL Server

To use Sequence first SQL Server Management Studio (SSMS) and expand the Object explorer, under programmability you will see the sequence node.



If you right click the sequence and select new, you will be taken to the below screen which has all the attributes for the sequence.



Since Sequence is a database object, it needs to be assigned to a schema. It has a data type which can be int, bigint, tinyint, smallint,numeric or decimal. The start value and increment as similar as to the values you will be familiar with using Identity.
The Minimum and maximum are boundaries for the sequence. When  the cycle option is set you have the ability to re-use sequence numbers.
Similarly, Sequences can be created using T-SQL as follows.

IF EXISTS (SELECT * FROM sys.sequences WHERE name = N’EmployeeSeq’)
                DROP SEQUENCE EmployeeSeq;
GO

 CREATE SEQUENCE EmployeeSeq AS tinyint
                START WITH 0
                INCREMENT BY 5;
GO


Now let us see how we can integrate this with an Insert statement.
First we will create a table to incorporate the sequence we created.

CREATE TABLE Employee
(ID tinyint,  Name varchar(150) )


Then we will insert:

INSERT INTO Employee
(ID,Name)
VALUES
(NEXT VALUE FOR EmployeeSeq, ‘Dinesh’)
INSERT INTO Employee
(ID,Name)

VALUES
(NEXT VALUE FOR EmployeeSeq, ‘Asanka’)


Note that you are now using the EmployeeSeq sequence object for the insert.

Restarting a Sequence

Can we restart Sequence in SQL Server 2011? Of course you can. In identity you previously used Reseed for this.

ALTER SEQUENCE dbo.EmployeeSeq
RESTART WITH 2;


Above statement will allow you to restart the sequence from 2.

Sequence Transactions

Another issue is, what will happen to the next number of a sequence when the transaction is rolled backed.
Let us check this. First we will see what is the current value of  the sequence.

SELECT current_value FROM sys.sequences
WHERE Name = ‘EmployeeSeq’


Since, we have restarted the sequence this will return a value of 2 and now let us execute this in transaction and later we will rollback the transaction.

BEGIN TRAN
INSERT INTO Employee
(ID,Name)

VALUES
(NEXT VALUE FOR EmployeeSeq, ‘Asanka’)

ROLLBACK TRAN

Again, we will check the next value for the sequence:

SELECT current_value FROM sys.sequences
WHERE Name = ‘EmployeeSeq’


Above query will return 7 which means the rollback statement does not rollback the next value for the sequence. This behaviour is the same as Identity.

CYCLE

 If you have set the cycle option on, your Sequence object will re-use numbers. Let us see this in an example. By stating CYCLE your sequence cycle option will be set to true.

CREATE SEQUENCE [dbo].[EmployeeSeq]
 AS [tinyint]
 START WITH 2
 INCREMENT BY 5
 CYCLE


 To see this option to work let us execute following statement.

SELECT
NEXT VALUE FOR EmployeeSeq
GO 100


Following image is a part of results you would get and you can see that after 255 it has restarted to 0 again.


OVER

Let us create a new sequence.

CREATE SEQUENCE SeqOrder AS tinyint
                START WITH 1
                INCREMENT BY 1
                MINVALUE 1
                NO MAXVALUE
                CYCLE;
GO


Now, let us run following query:

SELECT ID,Name,
NEXT VALUE FOR SeqOrder OVER (ORDER BY Name DESC) As [Order]
FROM Employee;


The results should be as shown below:



This means you can use Sequence as a running number in a query.
 

Performance of Sequence vs Identity

For me this is the best aspect of using Sequence. Performance wise it has advantage over the identity.
Let’s measure this.
I will create three tables; timing to measure the time, idt to insert data with identity and seq for insert data with sequence.

CREATE TABLE timing(
Instance varchar(50),
occ_time datetime default getdate())
CREATE table idt
(ID int identity(1,1), Des Varchar(100) )

CREATE table seq
(ID int  , Des Varchar(100) )


Then I will create two procs, insert_idt to insert data with identity and insert_seq to insert data with sequence.
Creating insert_idt procedure:

CREATE PROC insert_idt
AS
BEGIN
INSERT INTO idt
(Des)

VALUES (‘insert idt’)
INSERT INTO timing
(Instance)
Values (‘Insert idt’)

END

Creating procedure insert_seq:

CREATE PROC insert_seq
AS
BEGIN
INSERT INTO seq
(ID,Des)

VALUES (NEXT VALUE FOR SEQ5,’insert seq’)
INSERT INTO timing
(Instance)
Values (‘Insert seq’)

END

 Then I executed each proc 10000 times:

exec insert_idt
 GO 10000

 exec insert_seq
 GO 10000


Then we measure the timing for each batch:

SELECT CAST(MAX(occ_time) – MIN(occ_time) AS TIME) FROM timing
 WHERE Instance =’Insert idt’

 SELECT CAST(MAX(occ_time) – MIN(occ_time) AS TIME) FROM timing
 WHERE Instance =’Insert seq’ 


In this test, executing of the first batch of procs (using Identity) took16.557 seconds and second (using Sequence) took 14.33 seconds, thus demonstrating the performance advantage of Sequence. This advantage will be much greater you are testing this in a table where there is a large number of records.

Limitations of Sequence

·         You are allowed to use NEXT VALUE FOR in a UNION ALL, but not in a UNION, EXCEPT, INTERSECT, or with DISTINCT. In which case you will get the following error message.
Msg 11721, Level 15, State 1, Line 3
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION (except UNION ALL), EXCEPT or INTERSECT operator.

·         In addition, I attempted to create a view with sequence and failed with following error message.
Msg 11719, Level 15, State 1, Procedure vw_1, Line 4
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.
·         An error will be thrown when the NEXT VALUE FOR function is used in a TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, ORDER BY, COMPUTE, or COMPUTE BY clause.
·         Finally an error is thrown if the NEXT VALUE FOR function is used in the WHEN MATCHED clause, the WHEN NOT MATCHED clause, or the WHEN NOT MATCHED BY SOURCE clause of a merge statement.

Final words

Note that these samples are from CTP1 and there may be some syntax and feature changes in the final release but this core feature will exist.

Author: Dinesh Asanka

Saturday, September 10, 2011

Adding Expires Header to static images in IIS 7

One of the things YSlow checks for is the expires HTTP header for static content such as images, script files and style sheets. Some sites always use custom HTTP handlers for serving scripts and stylesheets, only the static images have been a problem.

The problem

The problem is that with images on hosted environments on IIS 6, it’s impossible to control the serving of them without redirecting them through an HTTP handler. That’s not a good idea for several reasons:
  • It adds unnecessary overhead by going through the ASP.NET ISAPI
  • You need to add custom code to handle the requests
  • You need to change the URL from .gif to .gif.axd or similar
Here is what YSlow finds on Mads website that needs the expires header set to a far future date:



As you can see, it is all my static images that lacks the expires header.

The solution

If you run IIS 6 there is no good way of adding an expires header to images unless you have control over the IIS. If your site is hosted then you probably have no control at all. If you are using IIS 7 however, you can very easily add the header in your web.config’s system.webServer section like so:
<staticContent>
 <clientCache httpExpires="Sun, 29 Mar 2020 00:00:00 GMT" cacheControlMode="UseExpires" />
</staticContent>

What happens is that all static content will now have an expires HTTP header set to the year 2020. Static content means anything that isn’t served through the ASP.NET engine such as images, script files and styles sheets. This is one of the very easy tricks that will increase the performance of your site as well as your YSlow score.

Reference: .NET Slave

Make your ASP.NET Page Methods Secure

One of the most persistent misconceptions about ASP.NET’s page methods is the notion that they have some intrinsic protection against requests that don’t originate from the page where they’re defined. Since a page method’s code resides within a page’s code-behind file, it’s intuitive to assume that those methods benefit from some form of inherent security.

Unfortunately, that is not the case

Exploiting your page’s insecurities

In case it’s hard to believe these code-behind methods truly are so easily accessible, let’s take a look at a quick example. Let’s say you have a page method that returns sensitive business information, like so:
 
[WebMethod]
public static int SecretFormula() {
  return 42;
}

Assuming that method is defined in a Default.aspx file’s code-behind, located in a folder named TopSecret, here’s a bit of jQuery you could use to request the secret formula from any page on the site:
 
$.ajax({
  url: '/TopSecret/Default.aspx/SecretFormula',
  type: 'POST',
  contentType: 'application/json',
  data: '{}',
  success: function(result) {
    alert(result.d);
  }
});

In fact, you can even make that request from a plain HTML file. In this example, I’m making the request right from a simple HTML file named index.htm:



Not only is the request not originating from the ASPX file that contains the page method, that request wasn’t originating from an ASPX file at all!

Is this really a problem?

You might be asking yourself if this is really a problem to begin with. After all, you aren’t very likely to write client-side code that requests sensitive data unless you actually need that data, regardless of whether you write that code on an ASPX page, HTML page, or anywhere else.
The trouble is, a villain could land on any of your site’s pages, open up Firebug, and start probing for weaknesses like this one. In this case, the obscurity of the exact location of the page method might lead you to a false sense of security, but if an attacker has any knowledge of your system’s architecture then the door is wide open.
Even more troubling, as long as an attacker uses POST requests with an application/json Content-Type, they could use a tool as simple as Fiddler to interrogate your site’s services remotely. A few lines of server-side code, and your competition could set up a site driven by the page method APIs on your site that you thought were private!

A fixable problem

Page methods aren’t indefensible against the shenanigans of external interlopers though. You can secure them with ASP.NET’s built-in authorization mechanism, just as you would any other ASPX page (or ASMX service). In fact, if you secure an ASPX page with ASP.NET authorization, page methods defined in its code-behind are automatically equally secure.

To deny unauthenticated access to methods in our TopSecret folder, adding an authorization entry to the web.config in that folder is all that’s necessary:

<?xml version="1.0"?>
<configuration>
<system.web>
<authorization>
<deny users="?" />

</authorization>
</system.web>
</configuration>

Now, an unauthenticated user trying to access the SecretFormula method from index.htm (or anywhere else, without authenticating first) is denied access:


Attempting to request the same page method with jQuery after securing it.

Of course, there are a variety of ways to control access to these methods. You might test to see which role a user’s in and respond accordingly, or you might only need to verify some token stored in the Session.
Regardless of the particular mechanism, the key is to remember that none of this is automatic and that you must be mindful of regulating access to your page methods.

Reference: Encosia.

Friday, September 02, 2011

"The resource cannot be found" error in ASP.NET MVC 3

This week I downloaded the latest version of NerdDinner source code from codeplex for ASP.MVC 3 architecture. Opening the solution and executing, I got  "The resource cannot be found" exception. I was clueless as I just downloaded the latest source from the SVN and executed. Googling on it I learned that we can run into a problem when our requests are automatically redirected to a (possibly non-existent) login page.

To fix this, include the following lines in your web.config file:

For MVC 3 beta and MVC 3 RC1:
<appsettings> <add key="autoFormsAuthentication" value="false">
</add></appsettings>

For MVC 3 RC2:
<appsettings> <add
key="enableSimpleMembership" value="false"> </add></appsettings>