November 9, 2007 at 10:24 am
We have many instances on many servers. From a central point I'd like to be able to run some sort of blanket password reset.
I have a central list of instances, and the accounts I wish to alter the passwords for.
I'd like to do something like:
a) Make a connection to the remote instance/DB
b) issue some alter login statements,
c) report the success or failure
d) repeat until I've done all my changes.
My central server has been set up as a linkedserver to all the other others.
I've looked at a few postings about openquery and also dynamic SQL. So far it looks like I'd need to cursor through my table. create some dynamic SQL which contains the alter command and then use xp_cmdshell to invoke an isql/osql session. That will probably work but it seems very cumbersome.
I'd also like to know if I pass these alter login commands across what scope exists for network sniffing etc to see the passwords I'm setting the accounts to.
November 10, 2007 at 8:39 pm
As opposed to trying to do all of this through SQL Server, I would suggest scripting it using Perl, VBScript, or PowerShell. VBScript would be my last choice because it doesn't provide much in the way of error handling. You would also get the advantage of using languages which are procedural in nature and are designed to handle multiple branches.
With respect to your question about network sniffing, unless the communication is encrypted, it can be sniffed. If it's going across a private network, such as over network switches (think LAN), then someone would have to set up a span port, and that means they already have access to the networking infrastructure. If that's the case, all bets are off anyway. As far as the WAN is concerned, if you're going across the Internet and you're not using VPN, then at any point someone could, but there is some question as to likelihood, etc. Still, I wouldn't talk to a SQL Server directly across the Internet unless I had no other choice. With respect to WAN technologies on a private network, you can assume the line is as trusted as a switched infrastructure.
However, there are options with respect to ensuring the communication is encrypted, but they require change either on your SQL Server or your OS, or both. If SQL Server has an issued certificate, then SQL Server can use SSL for all communication. You just have to toggle secure communication on. Another option is using IPSEC. This is especially convenient if all the systems are in the same Active Directory forest, as Kerberos can be used to assist with the encryption process.
K. Brian Kelley
@kbriankelley
November 12, 2007 at 1:19 am
Thanks for the comments. Unfortunately I don't have access to, or skills in, the other languages you suggest. So anyone got any ideas for doing this within SQL server
November 12, 2007 at 6:52 pm
Within SQL Server this isn't very doable except by the means you describe (and SQLCMD is a good choice instead of isql/oswl if you're using SQL Server 2005 tools). Linked server connections are intended to manipulate data, not administer the system, so the linked server connection itself along with a 4 part naming convention doesn't get you there. It may be possible using OPENROWSET, since that passes the query as is through to the referenced datasource, but it doesn't use linked servers.
And VBScript is typically installed on systems unless your admin has intentionally uninstalled Windows Scripting Host.
K. Brian Kelley
@kbriankelley
November 14, 2007 at 1:52 pm
On a related note, it looks like Red Gate is coming out with a tool called SQL Multi Script in the next couple of weeks that handles this sort of situation. You might look for it on the Red Gate site. It's not there yet as it hasn't been released, but as soon as it is, I'm sure it'll show up there.
K. Brian Kelley
@kbriankelley
November 20, 2007 at 6:55 am
After doing some digging on options within SQL Server I ended up in another Thread on this site.
Do a search for OPENROWSET ALTER LOGIN. (Can't see any other way to link to another forum post).
The basic gist is that you can have a list of your servers, accounts and passwords and cursor through them using openrowset to send either the alter login command (SQL2005) or exec sp_passwd (SQL200), along with adequate error handling and logging of results.
Thanks for the additional info, and I hope others find these posts useful.
November 21, 2007 at 7:32 am
If you have multiple instances, I'd use SSIS.. you can call the package from a procedure and pass it parameters.
The package can then loop through the statement across all servers.. That being said.. It'd take a fair amount of scripting to fully automate it.. But, you can make it pretty basic and have it just run statements in parallel across all instances at the same time with one variable.
Personally, I would have great reservations about automation of this kind.. I would heavily consider using Windows Authentication where centralization of passwords can be done with AD or.. consider implementing certificates as modes of authentication for SQL 2005 servers where passwords are no longer necessary provided you have the appropriate cert.
Good luck.
January 14, 2008 at 9:41 am
First I'd like to say I agree that using Windows authentication is the best approach, but assuming you have no option than to use SQL logins and you want to administer them securely, here is how I would do it with a SQLCMD script.
You already have a list of servernames. You don't want to send the password text over the network, so you have to work with the hashed password. To do this in a script, you need your own varbinary-to-varchar conversion function. These functions rely on a dbo.Numbers table that contains integers from 1 to 1024 (or higher).
CREATE FUNCTION [dbo].[ByteToChar](@B AS BINARY(1)) RETURNS CHAR(2) AS
BEGIN
RETURN SUBSTRING('0123456789ABCDEF', (CAST(@b AS TINYINT)/16)+1, 1) + SUBSTRING('0123456789ABCDEF', (CAST(@b AS TINYINT)&15)+1, 1)
END
GO
CREATE FUNCTION [dbo].[BinaryToVarchar](@varbin AS VARBINARY(1024)) RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @s-2 VARCHAR(MAX)
SET @s-2='0x'
SELECT @s-2 = @s-2 + [dbo].[ByteToChar](SUBSTRING(@varbin, Number, 1))
FROM [dbo].[Numbers] (NOLOCK)
WHERE Number <= DATALENGTH(@varbin)
ORDER BY Number
RETURN @s-2
END
You can change the password on one server through the SSMS GUI to avoid putting it in a script. Then run this script on the same server to create an SQLCMD script to make the change on all servers.
DECLARE @pwd VARCHAR(2000)
SELECT @pwd = dbo.BinaryToVarchar(password_hash) from sys.sql_logins where name=' '
SELECT ':CONNECT ' + ServerName + '
ALTER LOGIN [ ] WITH CHECK_POLICY = OFF
BEGIN TRY
ALTER LOGIN [ ] PASSWORD = ' + @pwd + ' HASHED
PRINT ''Password change succeeded on ' + ServerName + '''
END TRY
BEGIN CATCH
PRINT ''Password change failed on ' + ServerName + '''
END CATCH
ALTER LOGIN [ ] WITH CHECK_POLICY = ON
' + 'GO'
FROM ServerList
Copy the results of this script to a new window, enable SQLCMD mode, and run it. The plain text password isn't in the script, so it can't be sniffed.
If you're dealing with multiple domains and must use SQL logins to some of the servers, it gets more complicated. You need to add -U and -P options to the :CONNECT command lines, but where can you store those securely? You might encrypt them as another column in your server table. Or if you assume that this script can be saved securely on your workstation, you might put them in the script like this:
FROM (
SELECT 'Sleepy' AS ServerName, ' -U TheDBA -P ?????' AS Credentials
UNION ALL SELECT 'Dopey' AS ServerName, ' -U TheDBA -P ?????' AS Credentials
UNION ALL SELECT 'Grumpy' AS ServerName, ' -U TheDBA -P ?????' AS Credentials
UNION ALL SELECT 'Sneezy' AS ServerName, ' -U TheDBA -P ?????' AS Credentials
UNION ALL SELECT 'Happy' AS ServerName, ' -U TheDBA -P ?????' AS Credentials
UNION ALL SELECT 'Bashful' AS ServerName, ' -U TheDBA -P ?????' AS Credentials
UNION ALL SELECT 'Doc' AS ServerName, ' -U TheDBA -P ?????' AS Credentials
) ServerList
If you include the actual passwords, you must be very confident that you have a secure location to save the script. If there are a small number of passwords, it would be better to make them replacable parameters and change them before running the script. This way the passwords are never written to a file, and are only visible for the time it takes you to run the script.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply