How do you reset authentication from SQL back to Windows?

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

How do you reset authentication from SQL back to Windows?

Postby SQL_ME_RICH » Fri Jun 01, 2012 1:16 am

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/vi ... 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.
SQL_ME_RICH
 
Posts: 64
Joined: Tue May 08, 2012 10:41 pm

Postby Marianne » Fri Jun 01, 2012 9:26 am

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: Select all
execute master..sp_addextendedproc sqbsetlogin , 'xp_sqlbackup.dll'


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


Cheers,

Marianne[/code]
Marianne Crowder
Red Gate Software Limited
Marianne
 
Posts: 45
Joined: Mon Oct 03, 2011 3:24 pm

Postby SQL_ME_RICH » Fri Jun 01, 2012 5:41 pm

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...
SQL_ME_RICH
 
Posts: 64
Joined: Tue May 08, 2012 10:41 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests