An unwanted surprise when changing the SQL Server 2005 Service Account

When changing the Microsoft SQL Server 2005 Service Account you might get back this rather alarming error message box: —————————WMI Provider Error—————————The process terminated unexpectedly. [0x8007042b]—————————OK   ————————— This is related to the problem documented in Books On Line on the page titled: ‘Start and Restarting Services’ (accessible in the index as ‘starting SQL Server’).  I quote from the paragraph…

9

A strange problem with ‘ADD SIGNATURE’ and the new SQLCMD command line utility

This one is a little odd:  But, when using the new ‘ADD SIGNATURE‘ DDL in SQL Server 2005, if you are using SQLOLEDB through the SQLNCLI (SQL Native Client) protocol stack, i.e. the stack the new command line utility SQLCMD uses, you will get the following ‘Incorrect syntax’ error when trying to issue an ‘ADD SIGNATURE’…

9

An error starting a SQL Server 2005 User Instance when using Remote Desktop

Late last week, I was using the new ‘User Instance‘ support in SQL Express 2005.  I was connecting from home using Remote Desktop to my main office machine, and when trying to connect to SQL Express, I was becoming increasingly frustrated with the following error: Error: ‘Failed to generate a user instance of SQL Server…

8

Protecting the SQL Server 2005 ‘Service Master Key’, the root of all encryption

Microsoft SQL Server 2005 introduces a new feature set, known internally as ‘Secret Storage’.  Secret Storage is the overall umbrella technology for the data encryption facilities in the SQL Server database engine, including the key management story.  The key management of a SQL Server 2005 instance is rooted in the Service Master Key (SMK), a…

6

Using SQL Server Management Studio (and Profiler) Tools with SQL Express ‘User Instances’

In the SQL Server 2005 Express Edition, available for free here, there is new connection string option: ‘user instance=true’.  When this connection string option is specified, the SQL Express instance you are connecting to (we call this the parent instance), spins of another instance of the sqlservr.exe process, as a client process (not a server service),…

2

Change the SQL Server Service Account password without Server Restart

A couple of weeks ago, we checked-in a feature to Microsoft SQL Server 2005, that allows the various Service Account passwords to be changed, using the SQL Server Configuration Manager, without having to restart the respective services. NOTE: It is going to be very important that you never use the plain old Windows Service Control Manager (SCM) to…

4

Setup Secure SQL Server Service Broker across databases in less than 10 seconds!

  One of the most interesting new features in SQL Server 2005 is Service Broker.  If you need more details on what it is and why you would want to use it, I will let the expert tell you.  But, if you want to get it setup and configured on your machine now then look…

12

Table Valued Function magic in SQL Server 2005 using the new SQL/CLR integration

In my last post, I said we would look at how we could return a table that contains a directory listing of all the .xml files on my machine.  Using the SQL/CLR LoadFile method we created in the last post, our goal is to come up with a single T/SQL statement that can dump all…

15

Getting those XML files into your brand spanking new relational database system

Up until now, XML documents have generally languished as mere files in the lowly file-system, our relational database systems haven’t seen them as important enough to warrant inclusion, up until SQL Server 2005 that is.  In SQL Server 2005, XML documents are now first class (data type) citizens; no more painful shredding required to hammer those…

13