Server Audit

  • Hi all

    I've been given the task of auditing our entire SQL estate as no-one is keeping track of the servers.

    I've cobbled together what I need (I'll post my code at the bottom) and it seems to work on SQL2005 upwards.

    We've got at least one server that's running 2000 and I can't see the changes I need to make to stop the code failing (I think I know where it is but I'm not 100% sure).

    This is my working code:-

    SET NOCOUNT ON;

    -- Drop all temp tables
    IF OBJECT_ID('tempdb..#TCPPorts') IS NOT NULL
      BEGIN
       DROP TABLE #TCPPorts;
      END;
    IF OBJECT_ID('tempdb..#SQLInstances') IS NOT NULL
      BEGIN
       DROP TABLE #SQLInstances;
      END;
    IF OBJECT_ID('tempdb..#Plat') IS NOT NULL
      BEGIN
       DROP TABLE #Plat;
      END;
    IF OBJECT_ID('tempdb..#Keyexist') IS NOT NULL
      BEGIN
       DROP TABLE #Keyexist;
      END;

    -- Create all temp tables
    CREATE TABLE #TCPPorts
    (
         PortType NVARCHAR(180),
         Port  INT
    );
    CREATE TABLE #SQLInstances
    (
         InstanceID   INT IDENTITY(1, 1)
                NOT NULL
                PRIMARY KEY,
         SQLVersion   VARCHAR(180),
         MajorVersionNumber INT,
         InstName    VARCHAR(180),
         Folder     VARCHAR(50),
         StaticPort   INT NULL,
         DynamicPort   INT NULL,
         Platform    INT NULL,
         MinMemory    INT NULL,
         MaxMemory    INT NULL,
         PhysicalMemory  INT NULL,
         Processors   INT NULL
    );
    CREATE TABLE #Plat
    (
         Id     INT,
         Name    VARCHAR(180),
         InternalValue VARCHAR(50),
         Charactervalue VARCHAR(150)
    );
    CREATE TABLE #Keyexist
    (
         Keyexist INT
    );

    -- Declare all required variables
    DECLARE
       @CurrID  INT,
       @ExistValue INT,
       @MaxID  INT,
       @sql   NVARCHAR(1000),
       @Platform VARCHAR(100);

    -- Get the platform data
    INSERT INTO #Plat
    EXEC xp_msver;

    -- Check if the platform is 64-bit or not
    SELECT
      @Platform =
    (
      SELECT DISTINCT
       1
      FROM
       #Plat
      WHERE Charactervalue LIKE '%X86%'
    );

    -- Use this to get the install folder details of the install
    IF @Platform IS NULL
      BEGIN
       INSERT INTO #SQLInstances
       (
         InstName
        , Folder
       )
       EXEC xp_regenumvalues
         N'HKEY_LOCAL_MACHINE',
         N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
       UPDATE #SQLInstances
        SET
         Platform = 64;
      END;
    ELSE
      BEGIN
       INSERT INTO #SQLInstances
       (
         InstName
        , Folder
       )
       EXEC xp_regenumvalues
         N'HKEY_LOCAL_MACHINE',
         N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
       UPDATE #SQLInstances
        SET
         Platform = 32;
      END;

    -- Check for keys and see if install is 64-bit or not
    INSERT INTO #Keyexist
    EXEC xp_regread
      'HKEY_LOCAL_MACHINE',
      N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';
    SELECT
      @ExistValue = Keyexist
    FROM
      #Keyexist;
    IF @ExistValue = 1
      BEGIN
       INSERT INTO #SQLInstances
       (
         InstName
        , Folder
       )
       EXEC xp_regenumvalues
         N'HKEY_LOCAL_MACHINE',
         N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';
      END;
    UPDATE #SQLInstances
    SET
      Platform = 32
    WHERE
      Platform IS NULL;

    -- Loop through all installs to get data
    SELECT
      @MaxID = MAX(InstanceID)
     , @CurrID = 1
    FROM
      #SQLInstances;
    WHILE @CurrID <= @MaxID
      BEGIN
       DELETE FROM #TCPPorts;
       SELECT
         @sql = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
              N''SOFTWARE\Microsoft\\Microsoft SQL Server\'+Folder+'\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
              N''TCPDynamicPorts'''
       FROM
         #SQLInstances
       WHERE InstanceID = @CurrID;
       INSERT INTO #TCPPorts
       EXEC sp_executesql
         @sql;
       SELECT
         @sql = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
              N''SOFTWARE\Microsoft\\Microsoft SQL Server\'+Folder+'\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
              N''TCPPort'''
       FROM
         #SQLInstances
       WHERE InstanceID = @CurrID;
       INSERT INTO #TCPPorts
       EXEC sp_executesql
         @sql;
       SELECT
         @sql = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
                             N''SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\'+Folder+'\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
                             N''TCPDynamicPorts'''
       FROM
         #SQLInstances
       WHERE InstanceID = @CurrID;
       INSERT INTO #TCPPorts
       EXEC sp_executesql
         @sql;
       SELECT
         @sql = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
                             N''SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\'+Folder+'\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
                             N''TCPPort'''
       FROM
         #SQLInstances
       WHERE InstanceID = @CurrID;
       INSERT INTO #TCPPorts
       EXEC sp_executesql
         @sql;
       UPDATE SI
        SET
         StaticPort = P.Port,
         DynamicPort = DP.Port
       FROM #SQLInstances SI
         INNER JOIN #TCPPorts DP
          ON DP.PortType = 'TCPDynamicPorts'
         INNER JOIN #TCPPorts P
          ON P.PortType = 'TCPPort'
       WHERE
         InstanceID = @CurrID;
       SET @CurrID = @CurrID + 1;
      END;

    -- Get final bits of info for audit
    UPDATE #SQLInstances
    SET
      MinMemory =
    (
      SELECT
       value
      FROM
       sysconfigures
      WHERE comment = 'Minimum size of server memory (MB)' -- Minimum SQL Usable memory
    ),
      MaxMemory =
    (
      SELECT
       value
      FROM
       sysconfigures
      WHERE comment = 'Maximum size of server memory (MB)' -- Maximum SQL Usable memory
    ),
      PhysicalMemory =
    (
      SELECT
       InternalValue
      FROM
       #Plat
      WHERE Name = 'PhysicalMemory' -- Total Usable memory on server (should be higher than Maximum SQL memory)
    -- to allow the O/S some memory for normal tasks
    ),
      Processors =
    (
      SELECT
       InternalValue
      FROM
       #Plat
      WHERE Name = 'ProcessorCount'
    ),
      SQLVersion = (LEFT(@@version, CHARINDEX('Copyright', @@version)-2)),
      MajorVersionNumber =
    (
      SELECT
       CAST(@@MICROSOFTVERSION / 0x01000000 AS INT)
    );

    -- Display the data
    SELECT
      SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ServerName
     , SQLVersion
     , MajorVersionNumber
     , InstName
     , Folder
     , StaticPort
     , DynamicPort
     , Platform
     , MinMemory
     , MaxMemory
     , PhysicalMemory
     , Processors
    FROM
      #SQLInstances;
    SET NOCOUNT OFF;

    Can anyone suggest any improvements and any ideas on why it's failing on SQL2000?

    Anyhelp gratefully received.

  • Have you tried making the change that you think you need to make?  What happens if you run the code directly against a SQL Server 2000 server - you must get error messages?

    John

  • John Mitchell-245523 - Monday, January 30, 2017 7:02 AM

    Have you tried making the change that you think you need to make?  What happens if you run the code directly against a SQL Server 2000 server - you must get error messages?

    John

    When I run this bit :-
      EXEC xp_regenumvalues
         N'HKEY_LOCAL_MACHINE',
         N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

    I get the following error:-
    Msg 22001, Level 16, State 0, Line 0
    RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'

    I've been into regedit on the offending machine and changed the path to what I think  is the right path (for the second part), I still get the same error.

  • You mean you changed the path in the script?  Strange that you still get an error if the registry path is correct.  Try stripping off one key at a time from the path and see when you first get the error.  That should help you identify where the part is that it can't find.  It may be that you've put in a simple space where a hard space is required, for example.

    John

  • Yeah, if I change the script to read:-
    EXEC xp_regenumvalues
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\Microsoft SQL Server\';
    I get two records returned.  These are:-
    Record 1
    Value
    InstalledInstances - Item #1
    Data
    MSSQLSERVER

    Record 2
    Value
    SqlMdacRegRefCount
    Data
    1

    If I then change the script to read:-
    EXEC xp_regenumvalues
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances - Item #1';

    I get the same error.
    I also get the error if I just use :-
    EXEC xp_regenumvalues
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances';

  • richardmgreen1 - Monday, January 30, 2017 8:43 AM

    Yeah, if I change the script to read:-
    EXEC xp_regenumvalues
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\Microsoft SQL Server\';
    I get two records returned.  These are:-
    Record 1
    Value
    InstalledInstances - Item #1
    Data
    MSSQLSERVER

    Record 2
    Value
    SqlMdacRegRefCount
    Data
    1

    If I then change the script to read:-
    EXEC xp_regenumvalues
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances - Item #1';

    I get the same error.
    I also get the error if I just use :-
    EXEC xp_regenumvalues
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances';

    OK, and that path is definitely valid and doesn't have any special characters masquerading as normal characters?  What about permissions - does it have the same access on it as other keys that you can see with the command?

    John

  • It certainly looks that way.
    I've got domain admin access and I can run this script on every other server (this is the only server I've come across so far that SQL2000 but I'm expecting more).

  • richardmgreen1 - Monday, January 30, 2017 8:51 AM

    It certainly looks that way.
    I've got domain admin access and I can run this script on every other server (this is the only server I've come across so far that SQL2000 but I'm expecting more).

    Didn't you say the key was Instance Names, not Installed Instances?  I'm not sure Domain Admins gives you access to every key in every registry.  But in any case, xp_regenumvalues runs in the security context of the SQL Server service account, I think, so you'll need to check what permissions that has.

    What happens if you do this?  What happens if you run just the reg query command from a command prompt? xp_cmdshell 'reg query HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names'

    John

  • I've just tried the following (with outputs):-
    xp_cmdshell 'reg query HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names'
    xp_cmdshell is not recognised as an internal or external command, operable program or batch file

    reg query HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names
    Invalid syntax (probably because of the spaces)

    reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names"
    ERROR: The system was unable to find the specified registry key or value

    reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server"
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
    InstalledInstances REG_MUL;TIsz MSSQLSERVER
    SqlMdacRegRefCount REG_DWORD 0x1
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\8.00.000
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\BKUPEXEC

    reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances"
    ERROR: The system was unable to find the specified registry key or value

  • Looks like 2000 has a value called InstalledInstances in the Microsoft SQL Server key, then.  You'll need to interrogate that.  Not sure what happens if you have more than one instance.  You probably get a comma-separated list, or something.
    reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server" /v InstalledInstances

    John

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply