How do you reset authentication from SQL back to Windows?

SQL_ME_RICH

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.

Marianne

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:

execute master..sp_addextendedproc sqbsetlogin , 'xp_sqlbackup.dll'

Then call the stored procedure with blank values to use Windows authentication:

execute master..sqbsetlogin '', ''

Cheers,

Marianne[/code]

SQL_ME_RICH

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...