The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Archive for September, 2011

The Dabbling Developer Series: Part1-Windows Service Management

Posted by Sudarshan Narasimhan on September 30, 2011


Now and then when I get some time I like to spend it writing code and I usually like to relate this to some work/activity that helps in my main work i.e. SQL Server. I have many small single-purpose applications/utilities written over time and I realized that having them saved on my PC is of no use. Don’t be surprised if you find C/C++ code samples posted on TheSQLDude. For the time being let me be the TheCDude and Long Live Dennis Ritchie & Ken Thompson!

 

Service Control Manager Test

I wrote some code to test if I can open the Windows Service Control Manager (SCM) for the local machine or remote machine with the current logged-in account. This helps me validate permissions or spot other errors when performing service related activities. For those of you who don’t know what SCM is read this article.

SCM is a system component that takes care of service management, service start-up and other service related activities.

You might ask, How did I come to care about SCM all of a sudden? What the hell does this have to do with SQL Server?

Let me explain. I came about SCM when a customer of mine came up with a SQL Server Setup issue. SmileWhen SQL Server setup goes about installing the various SQL Server components, one of the things it has to do is to create the SQL Server services like SQL Engine, RS, AS, Browser service, SQL Writer service etc. Obviously if setup is not able to do this, it will fail. For someone to create/delete/modify a Windows service, they need to talk to the Service Control Manager (SCM). How do you isolate whether the problem is with SQL Setup or some other component? This is how I ended writing this code to test SCManager.

If you want to perform windows service related activities, it works like this:-

1. You need to open the SCManager, which will let you access its database which has the list of registered services

2. If this succeeds, then depending on the operation you can either Open an existing service and modify/query its properties or create a new service and register it with SCM.

 

The code below is simple C code written on Visual Studio 2010. This has no copyright so feel free to re-distribute.


// scmantest.cpp : Defines the entry point for the console application.

//

 

#include "stdafx.h"

#include <windows.h>

#include <tchar.h>

#include <strsafe.h>

#include <stdio.h>

#include <conio.h>

 

#pragma comment(lib, "advapi32.lib")

#pragma comment(lib, "Kernel32.lib")

 

int _tmain(int argc, _TCHAR* argv[])

{

 if (argc < 2)

 {

  _tprintf(_T("Usage : %s name_of_machine_to_test"), argv[0]);

  return 1;

 }

 

     //CHAR lpMachine ;

    BOOL bReturn = FALSE;

    SC_HANDLE hScm;

 

 

    // Check that we can open the SCManager on the machine

    hScm = OpenSCManager(argv[1], NULL, SC_MANAGER_ALL_ACCESS);

    if (hScm)

    {

        bReturn = TRUE;

        printf("OpenSCManager completed successfully\n");        

        CloseServiceHandle(hScm);

    }

      else

      {

        _tprintf(_T("Could not OpenSCManager on %s. GLE = 0x%08x\n"),argv[1],GetLastError());

        return 1;

      }

 

 _getch();

 return 0;

}

 


The above code tries to Open the SCM and if that fails then it reports the Win32 error. You can Bing! this error to find out what action to take. The application scmantest takes 1 parameter which is the Machine Name. So if you PC is called WorkPC.domain.company.com, then you would run this from the command prompt like this

image

Here is an output for a non-existent machine and you can see that the Win32 Error reported is 0x000006ba (The RPC server is unavailable).

image

 

This can help isolate permission issues when trying to open SCM database as you need to have Administrator rights. Sometimes certain permissions to create might be missing which you will have to grant.

If you are a Windows Admin reading this then here are some command-line instructions to operate on Windows Services

1. Query the state of an existing Windows Service – sc query ServiceName

e.g. Checking state of default instance of SQL Server.
sc query MSSQLSERVER

SERVICE_NAME: MSSQLSERVER
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

If you specified an invalid service name, you will get this error,
[SC] EnumQueryServicesStatus:OpenService Failed 1060:The specified service does not exist as an installed server.

2. Create a new Windows Service – sc create …

Let me take an example of creating a SQL Server service

e.g.  Creating a Default instance of SQL Server

sc create MSSQLSERVER type= own start= demand error= normal binPath= D:\MSSQL\MSSQL.1\MSSQL\Binn\sqlservr.exe obj= LocalSystem DisplayName= "SQL Server (MSSQLSERVER)"

type –> Type of service you want to create. Leave it at OWN.
start –> Start type for the service. You can have it at automatic, disabled. We choose demand which means manual start-up.
binpath –> Complete path to the sqlservr.exe
obj –> Startup account for the service.
DisplayName –> can be anything, this is what services.msc will show us.

Note:-
a) There must be a space between ‘=’ and the value.
b) Double-quotes are mandatory is there is a space in the path.

 

For those of you who didn’t know this, the windows service entries are present in registry in HKLM\SYSTEM\CurrentControlSet\Services\. This is the same location from where services.msc reads and displays the list of services.

 

3. Using SUBINACL to check permissions for Windows Services:

SubInACL is a command-line tool that is available for download here and shows you security information about files, registry keys, and services.

e.g. Query permissions on the service for default SQL Server instance

subinacl /verbose /service MSSQLSERVER /display

/owner             =system
/primary group     =system
/audit ace count   =1
/aace =everyone         SYSTEM_AUDIT_ACE_TYPE-0x2
        FAILED_ACCESS_ACE_FLAG-0x80    FAILED_ACCESS_ACE_FLAG-0x0x80
        SERVICE_ALL_ACCESS
/perm. ace count   =6
/pace =system   ACCESS_ALLOWED_ACE_TYPE-0x0
        SERVICE_QUERY_CONFIG-0x1           SERVICE_QUERY_STATUS-0x4           SERVICE_ENUMERATE_DEPEND-0x8
        SERVICE_START-0x10                 SERVICE_STOP-0x20                  SERVICE_PAUSE_CONTINUE-0x40        SERVICE_INTERROGATE-0x80
        READ_CONTROL-0x20000               SERVICE_USER_DEFINED_CONTROL-0x0100
/pace =builtin\administrators   ACCESS_ALLOWED_ACE_TYPE-0x0
        SERVICE_ALL_ACCESS

4. Granting permissions using SUBINACL

e.g. Grant Full Control to TestUser1 on the default SQL Server instance’s service.

subinacl /service MSSQLSERVER /Grant=Domain\TestUser1=F
MSSQLSERVER : new ace for anselm\administrator
MSSQLSERVER : 1 change(s)

Other useful Resources

Using SC.EXE to Develop Windows NT Services
http://msdn.microsoft.com/en-us/library/ms810435.aspx

How to create a Windows service by using Sc.exe
http://support.microsoft.com//kb/251192


I hope this post was useful for both Developers as well as Administrators. Would appreciate any feedback on this post since I am thinking of posting other useful code samples in the future. As always stay tuned for more geeky info….

 

Posted in Code Samples | Tagged: , , , , , , , , , | Leave a Comment »

Getting legacy DTS Designer working with Management Studio for Pablo Picasso’s

Posted by Sudarshan Narasimhan on September 16, 2011


There are many of you SQL Developers and wizards out there still working on legacy DTS packages. Of course, we cannot live without SSMS (SQL Management Studio) and thus try to integrate the legacy SQL 2000 DTS Designer into it. But, life is not that easy and the entire procedure to get DTS Designer/Runtime working on SQL 2005/2008/R2 Management studio is quite cumbersome.

While I was working on setting this up myself, learnt quite a few tricks which I what this blog is going to talk about. At the end of this you should be able to both import, design and execute legacy SQL 2000 DTS packages (not SSIS) on a machine that has SQL 2005+ Management Studio installed. Read on and let free your picassonian designer skills with DTS packages.

Getting Started
First off, here is the link to the official documentation that talks about the support for SQL 2000 DTS packages on SQL 2008 R2. There are 2 types

1. Run-time support  (meaning I can execute DTS packages using dtsrun.exe)
2. Design-time support (meaning I can import, open & design DTS packages in SSMS)

How to: Install Support for Data Transformation Services Packages
http://msdn.microsoft.com/en-us/library/ms143755.aspx

Read the above article to get an understanding of what is possible and what is not (Don’t start/install anything yet) as this process can be quite tricky. My friends @ SQLServerFAQ have a nice post on this which is also something I would recommend reading here.

Ready, Set, GO!

 

Step-by-Step Instructions

1. You would need to install a few additional components, so please download the following packages and keep them ready.

a. Microsoft SQL Server 2000 DTS Designer Components
http://www.microsoft.com/download/en/details.aspx?id=11988

Download the package SQLServer2005_DTS.msi

b. SQL Server 2005 Backward Compatibility Components

Depending on the version of your SQL Server Management Studio (SSMS) you will have to download the corresponding package

SQL 2005 – http://www.microsoft.com/download/en/details.aspx?id=15748

SQL 2008/R2 – http://www.microsoft.com/download/en/details.aspx?id=8824

Download the package SQLServer2005_BC.msi. If you have a 64-bit machine, then you would need the x64 package because the x86 package will not let you install and you will get this error.

image

c. Make sure when you had installed Management Studio that you have chosen “Management Tools – Complete” during the setup for SQL 2008.

 

2. Install both the above packages in the same order as listed above. Once this is installed you will see that the following paths containing some DLL’s

C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn \
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\

3. Now, you need to edit the system PATH to ensure that the path for “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\” comes before the ..\90\Tools or ..\100\Tools folders. You can do this by going to My Computer –> Properties –> Advanced –> Environment Variables and editing the value for "PATH” as shown below.

image

Here is a sample path value from my working machine.

PATH=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Windows\System32\WindowsPowerShell\v1.0\

4. Okay, now comes the manual part. This step is the same as given in the MSDN article.

 

32-bit Machine (x86)

a. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ folder

b. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources\ folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033\ folder (for English locale). For example, for U.S. English, the lang_id subfolder will be "1033".

64-bit Machine (x64)

a. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ folder

b. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn\Resources\ folder to the %ProgramFiles(x86)%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033\ folder (for English locale). For example, for U.S. English, the lang_id subfolder will be "1033".

On a system with default settings,

%ProgramFiles(x86)% –> C:\Program Files (x86)\
%ProgramFiles% –> C:\Program Files\

 

5. After this is done I would suggest a reboot of the machine to ensure that if any pending file renames are present, they will be completed on a system restart. This is not mandatory, but just a suggestion. If you cannot reboot, then you need to close all instances of SSMS and re-open to test opening a DTS package.

TESTING

From SQL Server Management Studio, navigate to the Legacy folder in Object Explorer as shown below

image

If all goes well, then when you open a SQL 2000 DTS package (test.dts), then you will see the legacy DTS designer open up as shown below.

image

Still Not Working
If things go wrong, then you will get this error when you tried to open a DTS package,

image

Error Text

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

If you have faithfully followed the above procedure step-by-step then you will need to perform this additional step as well on those systems where you receive the above error. This is why I have not added this step as default since it does not occur on all systems.

1. Navigate to C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn and see if there is a file called stardds.dll present there.

2. Note down the file version, date and size.

3. If your Operating System is Windows Server 2008/R2 or Windows Vista/7, then you will need to replace the file present with the one given below. This is because Windows 2008+ (including Windows 2008 R2) requires the 2005.90.4035.0 version of stardds.DLL. Windows 2003 or earlier requires the 2000.80.2151.0/2282.0 versions of stardds.dll

a. Rename the existing stardds.dll to stardds.dll.original

b. Download the version of stardds.dll that applies to your OS

For Windows 2008/2008 R2/Vista/Win7 –> Download stardds.dll.4035  from HERE
For Windows 2003/2003 R2 –> Download stardds.dll.2282  from HERE

c. Copy the downloaded DLL to the location C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\ (for 64-bit systems) OR C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ (for 32 bit systems) and name it as stardds.dll

4. Close SQL Management Studio and re-open it and repeat the process of opening the DTS package and it should work now.

Caveat for DTS Packages contains Analysis Service (AS) Tasks
Apart from this, if any of the DTS packages have Analysis Services Cubes then you will still not be able to open these packages in SSMS. You will be faced with this error message

Error Source : Microsoft Data Transformation Services (DTS) Package
Error Description : Invalid class string

The backward compatibility package do not include design-time support for modifying DTS packages that contain the SQL Server 2000 Analysis Services Processing task and also do not include support for the SQL Server 2000 Data Mining Prediction Query task. So, if your DTS package has either "AS Processing Task" or "Data Mining Prediction Query task", then we cannot open/design these packages in SSMS 2008/R2

If you want to be able to do so, then you will have to install the following SQL Server 2000 components from SQL Server 2000 media

1. Analysis Manager
2. Decision Support Objects
3. Client Components

You don’t the AS Server component, just the above will suffice. Once you install these components you then did the get the following DLL’s present on the machine,

msmdtsp.dll   –> for Analysis Services Processing Task
msmdtsm.dll  –> for Data Mining Prediction Task

Close and re-open SSMS and you should be able to open the package now.

 

Phew! This was quite a long post indeed but I think I have covered most issues one could run into. If you come across any other issues, leave a comment behind and I will try and see if that can be resolved.

As always stay tuned to the TheSQLDude for more….

Posted in Tools | Tagged: , , , , , , , | 42 Comments »

Did you know–SQL 2000 isn’t supported on Windows 2008+ ?

Posted by Sudarshan Narasimhan on September 1, 2011


I’m not sure how many of you are aware of this but I happened to talk to quite a few DBA’s who were hell-bent on installing SQL Server 2000 on Windows 2008 (don’t ask me why). But for those of you who are planning to do this, DON’T, because it isn’t supported.

SQL Server 2000 and below are not supported on Windows Vista/Windows Server 2008 and above (which includes Windows 7/Windows Server 2008 R2). Here is the official blog post by the SQL Releases team on this,

http://blogs.msdn.com/b/sqlreleaseservices/archive/2009/10/23/sql-server-on-windows-7-and-windows-server-2008-r2.aspx

Stay out of trouble and upgrade Smile.

Posted in General Thoughts | Tagged: , , , , , | 1 Comment »