Change login from SQL Authentication to Windows Authentication


#1

I want to change to solve authentication issues.

  1. If I just change the button it fails Test Connection.
  2. If I also remove the old User Name and Password it connects, but [Save & Close] is disabled.
  3. If I start a new backup job it automatically fills in the SQL Authentication information and I’m back where I started.

#2

Hi Fred. After you switched from SQL to Windows Authentication, can you fill in your current username and password?


#3

Hi Ruslan,

It’s already filled in from the SQL Server Authentication. If I just change to Windows Authentication (leaving it filled in) then Test Connection fails. If I then remove the user name and password it connects, but the [Save & Close] button is disabled so I can’t save the information.


#4

The idea is: after you change to Windows authentication, delete your SQL authentication, fill in your Windows authentication.


#5

I had the same problem, when I changed from Local Server to Remote Server.
My solution was to add a SPACE after the name of the server to back from…

Eg. “MSSQL10” to "MSSQL10 "

I use IP numbers to access my SQL Server, but by Adding a Space after the Last IP digit, it worked for me.

eg. “127.0.0.1” to "127.0.0.1 "

Hope this will help you…
The solution is known with the SqlBackupFTP team… They gave me this solution.

K.R.
Rolf Thomassen


#6

#7

Hi Ruslan,
I uninstalled and reinstalled; the default, when I opened “Connect to Server” was “Local SQL Server” and “.\SQLEXPRESS” (which are correct); Windows Authentication was checked and “DESKTOP-TH7P8CP\Fred” was the user name (the “Windows authentication”, as you suggested and I didn’t understand – it’s all on a PC in my home and I don’t have it passworded).

This failed on the nightly backup with this error message: “Job execution error: [SRV:8007#13] Authorization failed. … blank passwords aren’t allowed, …”. It ran fine on a manual start with SSMS open.

I wanted to change to Windows Authentication because I wanted to run Ola Hallengren’s checkdb code and was unable to find any SQL Server authentication that would allow it to run before the backup. However, it runs fine from a batch job so I’m going to do it that way. (SQL Server Agent isn’t part of the free Express version.) As I don’t want/need to password my PC I’ll revert to SQL Server authentication. When I install this on my client’s server I’ll use Windows Authentication as it is passworded, of course.

I also want to run some maintenance code after the backup, but there’s no indication in the log file that it ran, while there were entries for the “before” code. I’ll modify the “after” code to make a table change and find out for sure this evening – and do another batch job if it doesn’t.

Thanks for the help; you can marked this closed.
Rolf, thanks for the “space” idea; I’ll file it and expect to use it someday.

Best,
Fred

P.S. It would be easier to provide the text of error messages if it were possible to copy/paste from your logs.


#8

Hi Fred,

Thanks for the update.

You can click “Export” button at your backup job log window, then your backup job log will be exported in *.txt.