Pro SQL Server 2005 Reporting Services

SQL Server Reporting Services 2005 is the latest version of the reporting technology from Mcft, providing the means to design, author, render, and deploy business reports to users, customers, and employees, via the web or the company intranet. The reporting engine is built in to the SQL Server 2005 database (and provided as a free add-on with SQL Server 2000) and the report manager is integrated with Visual Studio 2003, providing an effective and familiar environment for all SQL Server and .NET developers. The book was written in parallel with a migration project, moving some 200 professional healthcare reports from various reporting architectures to SQL Server Reporting Services, and so is steeped in the day-to-day challenges, discoveries, and successes involved in delivering a successful reporting solution with this technology. In this book, you will find step-by-step guides, best practices, and real code examples that cover all of the common Reporting Services tasks.

The book can be downloaded from here

Disclaimer: I assume no responsibility for any user downloading this e-book from my blog. This book is not hosted on my blog and I am just posting a link to another url where I found the book. Download the e-book at your own responsibility. I am just sharing the information I have. I suggest that don't use this e-book for commercial purpose and support the author by buying the print version.

Command-Line Utilities in SQL Server 2005

1) The sqlcmd utility allows you to enter Transact-SQL statements, system procedures, and script files at the command prompt. This utility uses OLE DB to execute Transact-SQL batches.
2) The sqlwb utility opens SQL Server Management Studio. If specified, sqlwb also establishes a connection to a server, and opens queries, scripts, files, projects, and solutions.
3) The profiler90 utility launches the SQL Server Profiler tool. The optional arguments listed later in this topic allow you to control how the application starts.
4) You can access Database Engine Tuning Advisor at the command prompt by using the dta.exe file, or through the application's graphical user interface (GUI). The command-line utility lets you incorporate Database Engine Tuning Advisor functionality into scripts and software programs. The dta utility also takes XML input. The Database Engine Tuning Advisor GUI makes it easy to view existing tuning sessions tuning recommendations.
5) You can use the Execute Package Utility dialog box to specify package run-time configurations and run packages on the local computer. You also can use this utility to generate command lines for use with the dtexec command prompt utility.
6) The dtutil command prompt utility is used to manage SQL Server 2005 Integration Services (SSIS) packages. The utility can copy, move, delete, or verify the existence of a package. These actions can be performed on any SSIS package that is stored in one of three locations: a Microsoft SQL Server database, the SSIS Package Store, and the file system. The package and its storage type are identified by the /SQL, /FILE, and /DTS options.
7) The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology.

Siddharth Mehta
[MSc(I.T), MCP, MCAD, MCSD]

Using Profiler in SQL Server 2005 to measure performance

SQL Profiler along with performance counters statistics can be used to measure the performance.
The new Profiler tool that ships with SQL Server 2005 now provides the feature to compare the trace with the performance counters. The best way to experiment this example is to load your SQL Server instance with lot of activity, record the trace as well as the performance counter and compare the same in the SQL Server Profiler tool.

Download this tutorial which explains in details all the steps with relevant snapshots of all the steps.

Siddharth Mehta
[MSc (I.T.), MCP, MCAD, MCSD]

SQL Server 2000 Password Security

How does SQL Server store passwords?
SQL Server uses an undocumented function, pwdencrypt() to produce a hash of the user's password, which is stored in the sysxlogins table of the master database. This is probably a fairly common known fact. What has not been published yet are the details of the pwdencrypt() function. This paper will discuss the function in detail and show some weaknesses in the way SQL
Server stores the password hash. In fact, as we shall see, later on I should be saying, 'password hashes'.
What does an SQL password hash look like?
Using Query Analyzer, or the SQL tool of your choice, run the following query
select password from master.dbo.sysxlogins where name='sa'
You should get something that looks similar to the following returned.
0x01008D504D65431D6F8AA7AED333590D7DB186
3CBFC98186BFAE06EB6B327EFA5449E6F649BA954AFF4057056D9B
This is the hash of the 'sa' login's password on my machine.
What can we derive from pwdencrypt() about the hash?
Time
The query select pwdencrypt('foo')produces
0x0100544115053E881CA272490C324ECE22BF17
DAF2AB96B1DC9A7EAB644BD218969D09FFB97F5035CF7142521576
but several seconds later repeating the query select pwdencrypt('foo') produces 0x0100D741861463DFFF7B5282BF4E5925057249
C61A696ACB92F532819DC22ED6BE374591FAAF6C38A2EADAA57FDF
The two hashes are different and yet the input, ‘foo’, is the same. From this we can deduce that time must play an important part in the way password hashes are created and stored. The design reasons behind this will be such that if two people use the same password then their hashes will be different - thus disguising the fact that their passwords are the same.
Case
Run the query select pwdencrypt('AAAAAA')
which produces
0x01008444930543174C59CC918D34B6A12C9CC9E
F99C4769F819B43174C59CC918D34B6A12C9CC9EF99C4769F819B.
Now, we can note that there are probably two password hashes here. If you can't spot it immediately let me break it down
0x01008444930543174C59CC918D34B6A12C9CC9E
F99C4769F819B43174C59CC918D34B6A12C9CC9EF99C4769F819B
As can be seen, the last 40 characters are the same as the penultimate 40 characters. This suggests that passwords are stores twice. One of them is the normal case sensitive password and the other is the upper-cased version of the password. This is not good as any one attempting to crack SQL passwords now has an easier job. Rather than having to break a case sensitive password they need only go after the upper-cased version. This reduces the number of characters they need to attempt considerably.
Clear Salt
From what we know already, that changes in time will produce a change in the hash, there must be something about time that makes the password hashes different and this information must be readily available so when someone attempts to login a comparison can be performed against the hash derived from the password they supply and the hash stored in the database. In the breakdown of results from pwdencrypt() above the 84449305 portion is this piece of information.
This number is derived in the following fashion. The time () C function is called and used as a seed passed to the srand() function. srand() sets a start point to be used for producing a series of (pseudo)random numbers. Once srand is seeded the rand() function is called to produce a pseudo random number. This number is an integer; however SQL server converts this to a short and sets it aside. Lets call this number SN1. The rand() function is called again producing another pseudo random integer which, again, is converted into a short.
Let's call this number SN2. SN1 and SN2 are joined to produce an integer. SN1 becoming the most significant part and SN2 the least significant part : SN1:SN2 to produce a salt. This salt is then used to obscure the password.
Hashing the password
The user's password is converted to it's UNICODE version if not already in this form. The salt is then appended to the end. This is then passed to the crypt functions in advapi32.dll to produce a hash using the secure hashing algorithm or SHA. The password is then converted to its upper case form, the salt tacked onto the end and another SHA hash is produced.
0x0100 Constant Header84449305 Salt from two calls to rand()43174C59CC918D34B6A12C9CC9EF99C4769F819B Case Sensitive SHA Hash43174C59CC918D34B6A12C9CC9EF99C4769F819B Upper Case SHA Hash
The Authentication Process
When a user attempts to authenticate to SQL Server several things happen to do this. Firstly SQL Server examines the password entry for this user in the database and extracts the "salt" - 84449305 - in the example. This is then appended to the password the user supplies when attempting to log in and a SHA hash is produced. This hash is compared with the hash in the database and if they match the user is authenticated - and of course if the compare fails then the login attempt fails.
SQL Server Password Auditing
This is done in the same manner that SQL Server attempts to authenticate users. Of course, by far the best thing to do is, first off, is attempt to brute force the hash produced from the upper-cased version. Once this has been guessed then it is trivial to workout the case sensitive password.
Acknowledgements: The above articles was from a pdf document published by www.ngssoftware.com on 24th June, 2002.

My MCSD Track for Microsoft .Net

Hi All,

Upon the request of many readers of my blog, below is the track in reverse chronology which I had completed for my MCSD. I had appeared all the exams with the language of choice as C# (wherever applicable).

1) 70-300 Analyzing Requirements and Defining Microsoft .NET Solution Architectures
2) 70-340 Implementing Security for Applications with Microsoft Visual C#® .NET
3) 70-320 Developing XML Web Services and Server Components with Microsoft Visual C# .NET and the Microsoft .NET Framework
4) 70-315 Developing and Implementing Web Applications with Microsoft® Visual C#™ .NET and Microsoft® Visual Studio® .NET
5) 70-316 Developing and Implementing Windows®-based Applications with Microsoft® Visual C#™ .NET and Microsoft® Visual Studio® .NET

70-316 and 70-315 were quite easy.
70-320 was a challenging one, but I rate the toughness of this paper to be 6/10

70-340 was the hardest paper that I had ever appeared. I just got skipped with it. It was so so tough and it is one of the rarest paper that professionals appear. But it's an excellent paper to appear for those who may be interested in designing and developing security for applications.

70-300 was also a thrilling and tough one. It requires how microsoft expect you to answer on case-studies and not on your personal or any ethical experiences.

Dear Readers, if you feel my post to be helpful, please feel free to add comments to my post.

Siddharth Mehta
[MSc(I.T.), MCP, MCAD, MCSD]

Intellisense for SQL Server Query Analyzer

Friends,
Are you looking for features like these as in the image below ??


Click Here to download a completely FREE cool utility from Red-Gate software, that provides Intellisense for SQL Server and other features like
Code completion for fast, accurate script building, Discoverability in SQL Server query creation Keyword formatting, code snippet integration other extended features.

The tool/add-in is called SQL Prompt and is Free until 1st September, 2006 as published by Red-Gate Software. SQL Prompt works with Microsoft Query Analyzer, SQL Server 2005 Management Studio, Visual Studio 2005, Visual Studio .NET 2003, SQL Server 2000 Enterprise Manager, UltraEdit32.

Siddharth Mehta.
[MSc(I.T.), MCP, MCAD, MCSD]

SQLiMail Overview

SQLiMail in SQL Server 2005 is a replacement of SQLMail of SQL Server 2000. It still exists in SQL Server 2005 but is only meant to be for backward compatibility only.
SQLiMail does not need: Extended MAPI, Outlook, to execute mail calls in the server process.
Also it is supported on a cluster, and it does support SMTP. In fact it uses SMTP to send e-mails. E-mails having mail-attachments can now also be sent. SQLiMail uses ServiceBroker to deliver messages to avoid blocking a stored procedure.
One of the striking feature is that it has a feature of failover accounts. Using this, it's possible to specify more than one SMTP server per profile. So in case of unavailability of the primary SMTP server, the next one can be used to deliver e-mails.

Siddharth Mehta
[MSc(I.T.), MCP, MCAD, MCSD]

Enhancements in SQL Server 2005 - Part III

Enhancements in full-text search are as follows:

1) Support for full text search against linked servers
2) No limitations of using either one or all the columns in a table, you can now specify whichever columns you require in the column_list argument.
3) Full-text queries using a different language to the default column language.
4) Full-text catalogs can now be backed up and restored with or without the database data.
5) Full-text catalogs are included in attach and detach operations.
6) Full-text indexes can be created on XML data.
7) Performance of index population is improved.

SQL Server Integration Services

SQL Server Integration Services (SSIS) provides a comprehensive solution for transferring and transforming data between diverse data sources.

1) SSIS has been redesigned to separate package-control flow from data flow. Two engines have been introduced to handle these aspects of data transformation: the SSIS Run-time engine and the SSIS Data-Flow engine.
2) SSIS Designer is hosted in Business Intelligence Development Studio or SQL Server Management Studio.
3) Provides many built-in transformations
4) Management and Deployment has become easy as you can choose to deploy a SSIS package using design time deployment or the SSIS Installer.

Siddharth Mehta
[MSc(I.T.), MCP, MCAD, MCSD]

Enhancements in SQL Server 2005 - Part II

Analysis Services available in SQL Server 2005 has a lot to offer for the Business Intelligence platform. Microsoft has come up with a new Business Intelligence Studio. Analysis services makes it possible to create datawarehouses and also mine out data from OLAP data warehouses. Enhancements to Analysis services are as follows:

1) With the Business Intelligence Development Studio, it has become lot easy to use and implement this feature similiary like Visual Studio.
2) Multiple instance of Analysis services can now be implemented. I guess there's a limit of maximum 50 instances, still I am not sure of this limit.
3) New features KPI - Key Performance Indicators, support for stored procedures are avialable that enable business for making powerful analysis and decision making.
4) As published by Microsoft, Analysis services now supports Microsoft Naive Bayes algorithm which would enhance the performance of data mining.
5) Even for development there are major enhancements like Integration with CLR, XML for Analysis (XMLA), Enhanced MDX, ADOMD.Net API, Analysis Management objects, and Analysis Services Scripting Language.
6) There's a new support available now for development in live mode as well as project mode. Development in project mode makes it possible to make and test changes without affecting client's production systems.

Siddharth Mehta
[MSc (I.T.), MCP, MCAD, MCSD]

Enhancements in SQL Server 2005 - Part I

Enhancements in SQL Server 2005 worth consideration are for the following features: Database Engine, Analysis Services, Integration Services, Full Text Search, and Replication

Database engine is the heart of SQL Server. If provides all the services for adminstering the data right from storage to backing up the data. There has been many many great improvements in the database engine.

1) The first among them is the xml datatype. There is also good support for using XQuery expressions.
2) With the integraton of CLR with the database engine, it's now possible to build database objects with the language of your choice like VB.Net or C# for example.
3) Table partioning has improved a lot which yields performance gains.
4) DDL Triggers are a new feature avaialable in SQL Server 2005. These triggers get triggered when DDL statements are executed.
5) MARS i.e. Multiple Active Result Sets allows more than one pending request per connection.
6) A completely new secuirty architecture is available which provides enhanced database protection.

In my next post, I would come out with Part II - Analysis Services, which would summarize the new improvements in Analysis Services.

Siddharth Mehta
[MSc (I.T.), MCP, MCAD, MCSD]

Recompilation in SQL Server 2005

Hi,

Below described is the SQL Server 2005 Batch Recompilation Process.
I will come up with the explanation as well as my work-around on the same in my next post.



References
[Mar04] Arun Marathe, Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

Problem Installing SQL Server 2005 Express

Hi Friends,

When installing standalone SQL Server 2005 Express on XP Pro SP2, the install hangs at "Detecting Installed IIS". Even rebooting doesn't make any effect.

I searched numerous sites and found out a successful and effective solution for this.

The reason is that Norton/Symantec Anti-Virus and Firewall products causes the hang-up on "Detecting Installed IIS". Disable all Norton/Symantec Internet Security as well as Antivirus software. The install will get completed sucessfully.

Below are the recommendations from Microsoft for Installing SQL Server 2005

· Be sure the computer meets the system requirements for SQL Server 2005. For more information, see Hardware and Software Requirements for Installing SQL Server 2005.

· Review Security Considerations for a SQL Server Installation.

· Setup includes a preinstallation check that identifies unsupported configurations on installation computers and guides users to remedy blocking issues. For more information, see Check Parameters for the System Configuration Checker.

· Make sure you have administrator permissions on the computer where SQL Server will be installed. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share. To create a failover cluster, you must be a local administrator with permissions to logon as a service, and to act as part of the operating system on all nodes of the virtual server.

Security Note:

· If you modify permissions on a domain user account to run SQL Server Setup, be sure to revoke those permissions after Setup completes.

· Create one or more domain user accounts if you are installing SQL Server 2005 on a computer running Microsoft Windows XP or Windows 2003, and you want SQL Server 2005 to communicate with other clients and servers. For more information, see Setting Up Windows Service Accounts.

· Do not install SQL Server 2005 on a domain controller. For more information, see the "Isolation of Services" section in Security Considerations for a SQL Server Installation.

· Back up your current installation of SQL Server if you are running an instance of SQL Server on the computer where SQL Server 2005 is to be installed.

· Verify that the disk where SQL Server will be installed is uncompressed. If you attempt to install SQL Server to a compressed drive, Setup will fail.

· Exit antivirus software while installing SQL Server.

· Stop all services that depend on SQL Server, including any service using Open Database Connectivity (ODBC), such as Internet Information Services (IIS). Exit Event Viewer and registry editors (Regedit.exe or Regedt32.exe).

· Review all SQL Server installation options, and prepare to make the appropriate selections when running Setup.

Siddharth Mehta.
[ MSc (I.T.), MCP, MCAD, MCSD ]