November 11, 2005 at 2:01 pm
I am creating a stored procedure to change passwords (random values) for administrative SQL logins every month. Now I need some way to store and retrieve those passwords. One major hurdle is that I'll be running this on multiple servers, and I'd like to concatenate all of the results into a single location. The second hurdle is that I need this to be available to project managers who do not have network access to any of the servers in question.
Any suggestions?
The three options I can think of:
Insert the results, with openrowset, into a database on a central SQL server which managers have access to.
Dump the results to independent files on each server, and concatenate those files with an external process.
Email the results to a public folder, and let folks search for what they want (easy to implement, awful to use).
A fourth option is a combination of 1 and 2--dump the results into an excel file using openrowset, and manipulate that externally to present the data in a central location.
These all seem really clumsy. Am I missing something simple or brilliant?
November 14, 2005 at 8:00 am
This was removed by the editor as SPAM
November 14, 2005 at 10:25 am
Just curious as to why you are trying to save the password of generic administrative SQL logins. The auditors will not be happy that generic accounts with known passwords exists.
As an alternative approach:
1. Greate a domain group for SQL Server Administration.
2. Add the appropriate individual's account to the domain group.
3. Add the domain group to the servers with appropriate rights such as local administrator
4. Add the domain group to SQL Server with the appopriate rights such as system administrator role.
5. Finally, for generic accounts, such as "sa", change the password on a regular basis with a SQL Agent job to something very complex (GUIDs work fine) and do not record the password.
Based on this, each person uses their individual account to perform any activities and generic accounts are never used.
Of course, Service Accounts are a different situation where the password does need to be known. For SOX compliance, you may need to have someone in a different department enter the password whenever the service account must be used. As this usually only occurs when setting up a new SQL Server, this should be relatively rare.
I have been using this technique since 2000 at multiple clients and never had a problem with privileges while performing such activities as installs and applying service packs.
SQL = Scarcely Qualifies as a Language
November 15, 2005 at 1:49 pm
I'm not happy with it either, for the same reasons, but I'm somewhat limited in what I can change. One of the things I'm constrained to is SQL logins--life would be much simpler if I could use Trusted/Windows authentication!
I do appreciate your info, however! That's a tidy way to handle it, and I'll definitely push for moving to a model like that.
In the meantime, any suggestions on collecting data from multiple servers to a single central location?
November 15, 2005 at 2:46 pm
How familiar are you with DTS and Global Variables ?
I can send you an example DTS package that you could modify.
Here is the "driver" SQL code to call a DTS package once for each instance, passing the instance name as a global variable:
setnocount on
setxact_abort on
Declare@MachineNamesysname
,@InstanceNamesysname
,@SQLNamesysname
,@CmdTemplatevarchar(2000)
,@Cmdvarchar(2000)
delete from InventoryDTSLog
set@CmdTemplate =
'DTSRUN.exe /S&W /E /N"SQL Server Inventory - One Server" /ASourceServer:8=&SQLName'
Create table #DTS ( DTSOutvarchar(8000) )
DECLARE Instances_Csr CURSOR FOR
SELECT Instances.MachineName, Instances.InstanceName
from Instances
where Instances.InventoryInd = 'Y'
OpenInstances_Csr
WHILE1 = 1
BEGIN
FETCH NEXT from Instances_CSR into @MachineName , @InstanceName
IF @@FETCH_STATUS != 0BREAK
IF @InstanceName 'Default'
set @SQLName = @MachineName + '\' + @InstanceName
else
set @SQLName = @MachineName
Set@Cmd = REPLACE(@CmdTemplate,'&SQLName',@SQLName)
Set@Cmd = REPLACE(@Cmd , '&W' , @@servername )
INSERT INTO #DTS ( DTSOut )
exec master.dbo.xp_cmdshell @Cmd
Insert into InventoryDTSLog
( MachineName, InstanceName, DTSOut)
select @MachineName , @InstanceName, DTSOut
from #DTS
truncate table #dts
END
close Instances_Csr
deallocate Instances_Csr
SQL = Scarcely Qualifies as a Language
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply