Red Gate forums :: View topic - How do you reset authentication from SQL back to Windows?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

How do you reset authentication from SQL back to Windows?

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 60

PostPosted: Fri Jun 01, 2012 1:16 am    Post subject: How do you reset authentication from SQL back to Windows? Reply with quote

Running version 7 of SQL Backup (7.0.5.1), and have one of my instances that appears to have been previously configured when we were still on Backup 6, with SQL Authentication. I am drawing to this conclusion because the 'sa' password in this particular instance was recently changed, and now the backups will not kick-off when initiated. The error in the history log shows that it couldn't login with 'sa'.

I do not want SQL Authentication to be used for this process anyways, but the only reference I could find to changing this around is in the previous versions forum...

RedGate Backup job always fails
http://www.red-gate.com/messageboard/viewtopic.php?t=4823

Forgive the duplicate request for an answer here, but I need to get this fixed before the weekend arrives. Any and all help would be appreciated.

Thank you!

P.S. My question in that thread is still present. I am unable to locate this extended stored procedure, so I am wondering if there is a different approach to resolving this in SQL Backup 7.
Back to top
View user's profile Send private message
Marianne



Joined: 03 Oct 2011
Posts: 45

PostPosted: Fri Jun 01, 2012 9:26 am    Post subject: Reply with quote

Hi

Thanks for both your forum posts. Can I check whether you added the sqbsetlogin stored procedure before calling it with blank values?

If the SQL Backup Agent Service was set to use SQL Server authentication when SQL Backup 6 was installed, the stored procedure will not have been added. If the sqbsetlogin stored procedure was used to change to SQL Server authentication later, it may have been dropped after use (as per the instructions in the forum post you refer to and Security Model (http://www.red-gate.com/supportcenter/Content/SQL_Backup/help/7.0/SBU_SecurityModel). In both these cases you would need to add the stored procedure again before being able to call it.

If this is the case, try adding the stored procedure:
Code:
execute master..sp_addextendedproc sqbsetlogin , 'xp_sqlbackup.dll'


Then call the stored procedure with blank values to use Windows authentication:
Code:
execute master..sqbsetlogin '', ''


Cheers,

Marianne[/code]
_________________
Marianne Crowder
Red Gate Software Limited
Back to top
View user's profile Send private message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 60

PostPosted: Fri Jun 01, 2012 5:41 pm    Post subject: Reply with quote

That was it! Thank you again Marianne for your support, and my apologies that I didn't read that more closely in the original thread. I have things back and operational again.

Have a great weekend...
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group