SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hidden Dangers!


Hidden Dangers!

Author
Message
Antares686
Antares686
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: Moderators
Points: 49474 Visits: 803
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/hiddendang



Antares686
Antares686
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: Moderators
Points: 49474 Visits: 803

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





msurasky
msurasky
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 1

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!





Linda Bendele
Linda Bendele
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 1

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
Antares686
Antares686
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: Moderators
Points: 49474 Visits: 803
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.



Antares686
Antares686
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: Moderators
Points: 49474 Visits: 803
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).



msurasky
msurasky
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 1

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?





MattieNH
MattieNH
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4759 Visits: 901

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





Sarus1984
Sarus1984
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 109

@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


msurasky
msurasky
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 1

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

Life is good again





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search