May 10, 2006 at 1:48 pm
I'm rather perplexed about this one. Hopefully someone can explain what's really going on.
For SQL 2005 servers (and only 2005 -- SQL 2000 works with no problems), I cannot sucessfully change the SA password via OPENROWSET via SQLOLEDB. Here's the statement that I use:
select 1 from openrowset('SQLOLEDB', 'server=Server1;database=master;trusted_connection=yes', 'set nocount on; select ''Server1''; begin try alter login sa with password = ''XXXX''; raiserror(''User XXXX succesfully changed the SA password!'', 10, 1) with log; end try begin catch raiserror(''Error changing SA password by user XXXX!'', 10, 1) with log; end catch;')
Actually, the alter login statment works, but for some reason it keeps getting rolled back! When I do a Profiler trace, I see no errors of any kind. However, I see that there is a ROLLBACK TRANSACTION that always follows, no matter what I do. I tried everything I could think of, including explicit transactions.
However, I found that it works when I use MSDASQL! Here's the new statement:
select 1 from openrowset('MSDASQL', 'Driver={SQL Server};server=Server1;database=master;trusted_connection=yes', 'set nocount on; select ''Server1''; begin try alter login sa with password = ''XXXX''; raiserror(''User XXXX succesfully changed the SA password!'', 10, 1) with log; end try begin catch raiserror(''Error changing SA password by user XXXX!'', 10, 1) with log; end catch;')
I have been using SQLOLEDB with SQL 2000 servers to change passwords with sp_password with no problem. It's only when I tried to change a password on SQL 2005 that I saw the problem.
Does anyone have any idea why I keep getting that ROLLBACK TRANSACTION when I use SQLOLEDB, not MSDASQL, as the provider? I'm pretty stumped on this one.
Thanks!
May 11, 2006 at 2:25 pm
Hi,
This is only a guess: you are using Trusted Connections (your Windows authentication). It just could be that your token somehow could be used by MSDASQL but not SQLOLEDB. Did you try to specify SQL Server Standard user name and password for the connection?
Regards,Yelena Varsha
May 12, 2006 at 8:39 am
No, unfortunately that's not it. I tried creating a test user and using him in the connection string. No luck -- it still rolls back. Well, at least the MSDASQL works!
May 12, 2006 at 3:04 pm
FOUND IT!!!
I had a moment and tried your statement from SQL Server 2000 towards SQL Server 2005 with running a profiler in 2005. The first (almost) thing it did was
set implicit_transactions on
That means that you need to explicitly to commit transactions. When I tried the first time, I checked and I was not able to login with the new password of the user I was trying to change the password for. Then I added to your statement:
COMMIT TRANSACTION;
I added this right after
with password = ''usernewpassword'';
and ran the statement again. This time it worked. Actually it says in Profiler when you run the statement:
IF @@TRANCOUNT > 0 ROLLBACK TRAN
So if you commit the transaction it does not roll back I guess. Let me know if it helped.
Additionally I tried MSDASQL and as you can expect it DOES NOT run Set Implicit_Transactions on and also it does not run a couple of other SET statements. That is why it worked without Commit Transaction for you and for me to.
Regards,Yelena Varsha
May 15, 2006 at 9:43 am
You're right! You got it! You're awesome! :>
The only difference with what you had above is that I was going from a SQL 2005 to a SQL 2005, with no SQL 2000 involved. But it still works fine for me.
I guess this is a change in behavior from SQL 2000. However, it does make sense when reading BOL.
Thanks a ton, Yelena!
May 15, 2006 at 2:56 pm
Brian,
I think it may be a bug, I tried to look it up on the web today and found the following:
http://support.microsoft.com/?scid=http%3a%2f%2fwww.support.microsoft.com%2fkb%2f281633%2fen-us%2f
FIX: SQLOLEDB Provider incorrectly leaves transaction open
| Article ID | : | 281633 |
This article says that the fix is to obtain a recent fix for MDAC 2.6, but the APPLIES TO part goes up to MDAC 2.8 and I do run my computers on MDAC 2.81.
The workaround is to set these Implicit_Transactions to OFF as we did. Also it recommends to set a couple of other things to OFF like:
set implicit_transactions off SET NO_BROWSETABLE OFF
SET FMTONLY ON select ... SET FMTONLY OFF
and I do remember these other statements was set to ON in Profiler when going towards 2005 database.
What I don't understand why it works correctly when going towards 2000 servers.
Regards,Yelena Varsha
November 14, 2007 at 8:42 am
I'm looking at doing remote password changing on SQL2000 and SQL2005. Could you supply a copy of your script for the SQL2000 element. Also I want to loop through a list of about 100 servers. How can I code it to do that?
November 14, 2007 at 9:19 am
Hi Dave,
Is there a reason you want to change passwords on 100+ servers? The password change is a unique event individual for each users.
Regards,Yelena Varsha
November 14, 2007 at 9:43 am
Hello! Here's the main stored proc that I use (usp_ChanageSAPassword). I simply loop through a list of servers and call this procedure for each one. It's worked pretty well so far. Note that this procedure is only for the SA account. You can modify it, if you want, to change any arbitrary account's password. This is part of a system I built to securely manage (change, store, etc.) SA passwords. Hope it helps.
use Password_Admin
go
if exists
(
select *
from dbo.sysobjects with (nolock)
where
id = object_id(N'[dbo].usp_ChangeSAPassword')
and objectproperty(id, N'IsProcedure') = 1
)
drop procedure [dbo].[usp_ChangeSAPassword]
GO
create procedure [dbo].usp_ChangeSAPassword
/*======================================================================
Name of Procedure: usp_ChangeSAPassword
Date Created: 02/21/06
Created By: Brian Laws
Description: Change the SA password for a server.
It must exist in dbo.Servers and ChangeSAPassword must = 1
Returns: 0 = Success, 1 = Failure
Error codes:
======================================================================
Date ModifiedDeveloperDescription of Mod/ModNumber
======================================================================
05/10/06Brian Laws1. Change the command based on the SQL Server version
exec usp_ChangeSAPassword 'WS7927', 'test', null, 'M', '2005', 1
exec usp_ChangeSAPassword 'NT000142', 'december25', null, 'M', '2000', 1
exec usp_ChangeSAPassword 'NT000181', 'december25', null, 'M', '2000', 1
======================================================================*/
(
@ServerNamevarchar(50),
@NewPasswordvarchar(128),
@OldPasswordvarchar(128) = NULL,
@SecurityModechar(1) = 'M',
@SQLVersionvarchar(4) = '2000',--<<1 BHL: Need to pass in the SQL version
@Debugbit = 0
)
as
begin
set xact_abort off
set nocount on
declare @ReturnValuetinyint-- Value to return to the caller
declare @ErrorNumint-- Captured error number
declare @RowCountint-- Captured rows affected
declare @sqlvarchar(2000)-- Dynamic SQL to execute on the remote server
declare @FinalSQLvarchar(3000)-- The final dynamic SQL to execute (to enable dynamic connections)
declare @ConnectStringvarchar(100)-- Connection string for the openrowset
declare @Returnbit-- Return value for usp_CheckUserPassword
declare @ErrorMessagenvarchar(4000)-- Error message to raise to the user
declare @Providervarchar(50)-- Provider for the OPENROWSET<<1 BHL: Added
set @ReturnValue = 1-- Default to failure
-- First, validate the server against the Servers table and verify that it can update the SA password
if exists (select 1 from dbo.Servers with (nolock) where ServerName = @ServerName and ChangeSAPassword = 1)
begin
set @Provider = 'SQLOLEDB'
set @ConnectString = 'server=' + @ServerName + ';database=master;trusted_connection=yes'
--<<1 BHL: If SQL 2005, use ALTER LOGIN for the command and use MSDASQL as the Provider
if @SQLVersion = '2005'
begin
-- Need the fmtonly off, nocount on, and return the server name because openrowset requires a resultset
-- Need to set implicit_transactions to on so that the transaction isn't automatically rolled back
set @sql = 'set implicit_transactions on; set fmtonly off; set nocount on; select ''' + @ServerName + '''; '
set @sql = @sql + 'begin try alter login sa with password = ''' + @NewPassword + ''''
if @OldPassword is not null
set @sql = @sql + ' old_password = ''' + @OldPassword + ''''
-- The results will be logged as informational in the server's Application Log.
-- Explicitly commit or rollback the transaction
set @sql = @sql + '; if @@trancount > 0 commit transaction; raiserror(''User ' + system_user + ' succesfully changed the SA password!'', 10, 1) with log; end try begin catch if @@trancount > 0 rollback transaction; raiserror(''Error changing SA password by user ' + system_user + '!'', 16, 1) with log; end catch;'
end
else-- SQL 2000 or 7.0
begin
-- Need the fmtonly off, nocount on, and return the server name because openrowset requires a resultset
set @sql = 'set fmtonly off; set nocount on; select ''' + @ServerName + '''; declare @success bit; exec @success = master.dbo.sp_password @loginame = ''sa'', @new = ''' + @NewPassword + ''''
if @OldPassword is not null
set @sql = @sql + ', @old = ''' + @OldPassword + ''''
-- The results will be logged as informational in the server's Application Log
set @sql = @sql + '; if @success = 1 or @@error <> 0 raiserror(''Error changing SA password by user ' + system_user + '!'', 16, 1) with log else raiserror(''User ' + system_user + ' succesfully changed the SA password!'', 10, 1) with log'
end
--1>>
if @debug = 1
print @sql
-- Replace the single quotes with double quotes in the SQL query
set @sql = replace(@SQL, '''', '''''')
--<<1 BHL: Use the new @Provider variable
-- Create the final script to execute. Needed because openrowset does not allow variables for its arguments
set @FinalSQL = 'select 1 from openrowset(''' + @Provider + ''', ''' + @ConnectString + ''', ''' + @sql + ''')'
-- Execute only if debug is off
if @debug = 0
begin
begin try
exec(@FinalSQL)
-- At least one record should be returned. If not, there's an error.
if @RowCount = 0
begin
set @ErrorMessage = 'Error changing SA password for [' + @ServerName + '].'
raiserror(70003, 16, 1, @ErrorMessage)
end
else
begin
-- If mixed security mode, check to see if the change was successful
if @SecurityMode = 'M'
begin
exec @Return = dbo.usp_CheckUserPassword @ServerName, 'sa', @NewPassword, @ErrorMessage output
if @@Error > 0 OR @Return = 1
begin
set @ErrorMessage = 'Error checking SA password for [' + @ServerName + ']. Error: ' + @ErrorMessage
raiserror(70003, 16, 1, @ErrorMessage)
end
set @ErrorMessage = 'Succesfully changed the SA password for server [' + @ServerName + '].'
end
else
-- Since it is Windows Authentication only, it is not possible to verify the sa account.
set @ErrorMessage = 'Sent SA password change request to [' + @ServerName + ']. Check the server application log for success or failure.'
-- Update the SA password and changed date/time column
-- Encrypt the password first
open symmetric key Sym_Password_Admin decryption by certificate Cert_Password_Admin
update dbo.Servers
set
CurrentSAPassword = encryptbykey(key_guid('Sym_Password_Admin'), @NewPassword),
DateTimeChanged = getdate()
where
ServerName = @ServerName
close symmetric key Sym_Password_Admin
end
-- Send a success return code
set @ReturnValue = 0
end try
begin catch
-- Raise the error to the user and record in the Errors.dbo.Errors table
declare @error_number int, @error_state int, @error_severity int, @error_procedure nvarchar(126), @error_line int
select @error_number = isnull(error_number(), 70001), @error_state = error_state(), @error_severity = error_severity(), @error_procedure = error_procedure(), @error_line = error_line()
set @ErrorMessage = left('Error setting SA password for server ' + @ServerName + ' ! Error Message: ' + error_message(), 4000)
exec Errors.dbo.usp_WriteError @ErrorMessage, @error_number, @error_state, @error_severity, @error_procedure, @error_line
end catch
end
else
print @FinalSQL
end
else
set @ErrorMessage = @ServerName + ' is not authorized for automatic SA password change.'
print @ErrorMessage
-- Log the results of the execution
if @debug = 0
begin
insert into dbo.ActionHistory
(
ServerName,
ActionTaken
)
values
(
@ServerName,
@ErrorMessage
)
end
return @ReturnValue
end
go
November 14, 2007 at 9:44 am
Yelena Varshal (11/14/2007)
Hi Dave,Is there a reason you want to change passwords on 100+ servers? The password change is a unique event individual for each users.
Yes, I believe there is. We rely on a process I made to automatically change our SA accounts every 90 days based on our SOX standards. Doing this manually would be a nightmare, not to mention keeping track of that password.
November 14, 2007 at 11:06 am
Thanks very much Brian, just saved me a couple of days of work.
Changing the passwords, and maintaining a log of success and failure, is indeed to ensure I can keep the auditors & security teams happy, and to avoid a lot of manual work on a regular basis.
November 14, 2007 at 11:25 am
Brian and Dave,
these are good points and we do have SOX and FDA auditors. I am not sure they will be happy if we have uniformed SA passwords or SA passwords created by some sort of numbering scheme for all servers. We do keep things individual. How do you manage to have a unique SA password across the servers? Also, hopefully you keep the script under a source control?
Regards,Yelena Varsha
November 14, 2007 at 11:30 am
Yelena Varshal (11/14/2007)
Brian and Dave,these are good points and we do have SOX and FDA auditors. I am not sure they will be happy if we have uniformed SA passwords or SA passwords created by some sort of numbering scheme for all servers. We do keep things individual. How do you manage to have a unique SA password across the servers? Also, hopefully you keep the script under a source control?
I have a function which generates a random password, so this is called each time. The password is then stored in an encrypted column so that it can later be retrieved for use. If the database and server is secure enough, I believe the passwords will be secure as well. And yes, the code and everything is in VSS.
I'm right now going through my project and preparing it to be posted here in case you want to use it. I want to first go through and make sure all sensitive pieces are removed.
November 14, 2007 at 11:40 am
Actually, I'm thinking better of posting my whole solution here. Since this system is incredibly sensitive, I'm conerned that my posting it may expose any security holes and thus make my system a potential target. Instead, just let me know if you have any questions. Thanks.
November 14, 2007 at 12:40 pm
Yah, don't post.
Just give me an outline of the main idea, you may use a "Private Message" feature
When you say Encrypted Column you mean encryption with symmetric key or something or you mean the custom encryption function that can decrypt when need to read the data?
What probably concerns me most, is how many people have access to this information. You may reply only if you feel comfotable replying to this question.
Regards,Yelena Varsha
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply