Hidden Dangers!

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/hiddendang

  • Note: Until it is updated there is a correction to the code.

    dim App, cntServ, cntGrps

    Set App = CreateObject("SQLDMO.Application")

    For cntGrps = 1 to App.ServerGroups.Count

        For cntServ = 1 To App.ServerGroups(cntGrps).RegisteredServers.Count

      MsgBox App.ServerGroups(cntGrps).Name & " - "_

        & App.ServerGroups(cntGrps).RegisteredServers(cntServ).Name & " - "_

        & App.ServerGroups(cntGrps).RegisteredServers(cntServ).Login & " - "_

        & App.ServerGroups(cntGrps).RegisteredServers(cntServ).Password

     Next

    Next

    Set App = nothing

  • As usual, great article James, Good Job!

    I finished reading it and it let me so intrigued that I decided to give it an additional twist to the code and try to port it to a stored procedure (using sp_OA.. procedures to instantiate the DMO COM object model). This is the result

    CREATE PROC sp_CheckGroups AS

    BEGIN

    DECLARE @intReturnCode   int,

      @intDMO     int,

      @intServerGroups  int,

      @intGroupTotal   int,

      @intGroupCount   int,

      @intServerGroup   int,

      @intRegisteredServers int,

      @intServerTotal   int,

      @intServerCount   int,

      @intRegisteredServer  int,

      @strTmpChar    varchar(100),

      @strServerGroupName  varchar(100),

      @strServerName   varchar(100),

      @strServerLogin   varchar(100),

      @strServerPassword  varchar(100),

      @strDoing   varchar(100),

      @strSource   varchar(100),

      @strDescription  varchar(100)

      

     SET @strDoing = 'Creating the DMO object'

     EXEC @intReturnCode = master.dbo.sp_OACreate 'SQLDMO.Application', @intDMO OUTPUT

     IF @intReturnCode <> 0

      GOTO Error

     

     SET @strDoing = 'Creating the ServerGroups object'

     EXEC @intReturnCode = master.dbo.sp_OAGetProperty @intDMO, 'ServerGroups', @intServerGroups OUTPUT

     IF @intReturnCode <> 0

      GOTO Error

     SET @strDoing = 'Counting Registered Groups'

     EXEC @intReturnCode = sp_OAGetProperty @intServerGroups, 'Count', @intGroupTotal OUTPUT

     IF @intReturnCode <> 0

      GOTO Error

     SET @intGroupCount = 1

     WHILE @intGroupCount <= @intGroupTotal

     BEGIN

      SET @strDoing   = 'Creating the Server Group'

      SET @strTmpChar = 'Item(' + CAST(@intGroupCount AS VARCHAR) + ')'

      EXEC @intReturnCode = sp_OAMethod @intServerGroups, @strTmpChar, @intServerGroup OUTPUT

      IF @intReturnCode <> 0

       GOTO Error

      SET @strDoing = 'Creating the RegisteredServers object'

      EXEC @intReturnCode = master.dbo.sp_OAGetProperty @intServerGroup, 'RegisteredServers', @intRegisteredServers OUTPUT

      IF @intReturnCode <> 0

       GOTO Error

     

      SET @strDoing = 'Counting Registered Servers'

      EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServers, 'Count', @intServerTotal OUTPUT

      IF @intReturnCode <> 0

       GOTO Error

      

      SET @intServerCount = 1

       

      WHILE @intServerCount <= @intServerTotal

      BEGIN

       SET @strDoing    = 'Creating the Registered Server'

       SET @strTmpChar  = 'Item(' + CAST(@intServerCount AS VARCHAR) + ')'

       EXEC @intReturnCode = sp_OAMethod @intRegisteredServers, @strTmpChar, @intRegisteredServer OUTPUT

       IF @intReturnCode <> 0

        GOTO Error   

      

       SET @strDoing = 'Get the Name Property of The Group'

       EXEC @intReturnCode = sp_OAGetProperty @intServerGroup, 'Name', @strServerGroupName OUTPUT

       IF @intReturnCode <> 0

        GOTO Error

       SET @strDoing = 'Get the Name Property of the Registered Server'

       EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServer, 'Name', @strServerName OUTPUT

       IF @intReturnCode <> 0

        GOTO Error

       SET @strDoing = 'Get the Login Property of the Registered Server'

       EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServer, 'Login', @strServerLogin OUTPUT

       IF @intReturnCode <> 0

        GOTO Error

       SET @strDoing = 'Get the Password Property of the Registered Server'

       EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServer, 'Password', @strServerPassword OUTPUT

       IF @intReturnCode <> 0

        GOTO Error

       PRINT 'Group Name: ' + @strServerGroupName + CHAR(13) + 'Server Name: ' + @strServerName +

        CHAR(13) + 'Server Login: ' + @strServerLogin + CHAR(13) + 'Server Password: ' +

        @strServerPassword

       SET @intServerCount = @intServerCount + 1

       SET @strDoing = 'Destroy Registered Server Object'

       EXEC @intReturnCode = master.dbo.sp_OADestroy @intRegisteredServer

      END

      SET @intGroupCount = @intGroupCount + 1

      SET @strDoing = 'Destroy RegisteredServers Collection'

      EXEC @intReturnCode = master.dbo.sp_OADestroy @intServerGroup

      SET @strDoing = 'Destroy Registered Group Object'

      EXEC @intReturnCode = master.dbo.sp_OADestroy @intServerGroup

     END

     SET @strDoing = 'Destroy ServerGroups Object'

     EXEC @intReturnCode = master.dbo.sp_OADestroy @intServerGroups

     SET @strDoing = 'Destroy DMO Object'

     EXEC @intReturnCode = master.dbo.sp_OADestroy @intDMO

     -- IF we get here the normal way, don't do error

     GOTO Cleanup

    Error:

     EXEC sp_OAGetErrorInfo @intDMO, @strSource OUTPUT, @strDescription OUTPUT

     PRINT 'Error While ['  + ISNULL( @strDoing     , '' ) +

       '], Source ['  + ISNULL( @strSource     , '' ) +

       '], Description [' + ISNULL( @strDescription     , '' ) + ']'   

     

    Cleanup:

    END

    The procedure works OK, but when I run the script (as a .vbs file) I obtain 8 registered server (versus only one when I run the stored procedure). Do you see anything wrong on the SP? Can anybody here reproduce this strange behaviour?

    Keep writing this great articles, I really enjoy them!

  • I would like to understand how this is working in the first place. Does anyone have a minute to tell me more about how it works.

    Also I noticed that it will pick up any servers you have registered and will not pick up the 'local' server if it is noted possibly in the registry as such.

     


    Linda B

  • It's going to boil down to where it is executing from. For instance I do it on a Dev Ed on my machine here locally and I get the data in my EM. However if I put on a remote server it will be that of the EM installed on the remote server. Keep in mind local of code execution and security context may have some influence but I am usin my own login as the account for the SQL Service here.

  • Which are you refering to? The aritcle or the above SP code? Is there something specific you question. As for "will not pick up the 'local' server if it is noted possibly in the registry as such" it will if you have labled as local otherwise it will be the name registered in EM (which the data is stored in the registry).

  • The strange thing I notice is that both the script and my stored procedure are run against the same server and they show me different results. I'm using the same credentials (sysadmin) to run both the procedure and the script (using windows autentication) so the security context is the same too. So why the resultset is different?

    In other words

    RegisteredServers.Count (equals 8 in the script)

    EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServers, 'Count', @intServerTotal OUTPUT (equals 1 in the stored procedure

    Why?

  • Wow, who would have thought?

    I know I raise this point every time someone writes something like this:

    A user who saves any password is as scary as the ones who write them on sticky note nearby. 

    but, what are you supposed to do?  I have easily 100 passwords (and multiple login IDs), and I'd use the same password for each if I could (I know, security flaw) but just about everyone has different password composition rules.  Add to that passwords that have to be changed every 30/60/90 days, and there's no way I can keep track of all them without either saving them (which I don't do), or writing them down (which I do).

    So again, what are you supposed to do?

    Thanks

     

  • @msurasky:

    I guess it's not the same security context because, the *.vbs runs in the context with which you are logged in. The SQL-Prog runs as the SQL-Server-Service winnt-account (or sql-proxy-service account if you are not logged in as SA).

    //Sarus

  • Thanks Sarus and thanks James... that's exactly why I have this difference in terms of resultsets!!!

    Life is good again

  • Great article, a real eye-opener!  Nothing like seeing your own password in plain text screaming "here I am!" to wake you up in the morning!

    I agree with an earlier poster who said basically how is it possible to remember the zillions of passwords we must keep track of these days without either saving them in the dialogs or writing them down or using the same passwords in multiple systems.  I think that's the big security catch 22.  If you require users to change their password every month then you multiply the chance that they'll write it down just to get them to change it.  Have you really gained security then by enforcing such a rule?  Perhaps, but perhaps not. 

    This example certainly points out why not to save them in EM though!  I don't know if the same concern would necessarily apply to all "save password" checkboxes in other appliations though.

     

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

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