April 25, 2024 at 3:39 pm
I'm getting a error when using the Import and Export wizard to copy tables between a remote database and a local database using the Microsoft OLE DB Provider for SQL Server as Data Source. The remote DB uses SQL Server authentication and the local DB uses Windows authentication. Regardless of which database is the Source and which one is the Destination, the Import and Export wizard always fails on the step involving the local DB with the messsage
Login failed for user '0d0a06abc808\Guest'. (Microsoft OLE DB Provider for SQL Server)
Why is the Import and Export wizard using the Guest login? I don't use the wizard very often, but I know this didn't happen the last time I used it (about two months ago). The problem persists after restarting the SQL Server service or rebooting Windows. I'm an admin in the local machine and I have no problems connecting and administering the local DB from SSMS.
April 25, 2024 at 3:51 pm
As a test, try a sql account on the server where you’re attempting windows auth
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 25, 2024 at 5:24 pm
I created sysadmin user xxx . Now the wizard gives me this error:
Login failed for user 'xxx'
Which is funny because I am logged in as that user.
April 25, 2024 at 5:36 pm
I created sysadmin user xxx . Now the wizard gives me this error:
Login failed for user 'xxx'Which is funny because I am logged in as that user.
would see this if acct disabled, wrong pwd or instance not set to mixed mode auth
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 25, 2024 at 7:34 pm
I am logged in as that user, so acct is not disabled and mixed mode auth is enabled. I'm 100% sure the password was typed correctly. Notice I slightly edited my question to address what's at the root of this problem: Why is the wizard using the Guest account?
April 25, 2024 at 7:48 pm
What’s the full error for the login failure, err number, severity and state
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 25, 2024 at 8:03 pm
Never mind. I fixed the problem by enabling the Guest user in the local database. Funny thing is, I then disabled the same user and the error did NOT happen again. I'm still puzzled as to why the Import and Export wizard tried to use that login instead of the one that's currently logged in to the server.
April 25, 2024 at 8:13 pm
Would strongly advise not to enable that account
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 25, 2024 at 8:22 pm
See my previous comment, I disabled the account right after my test. In any case, it's my laptop at home, nothing critical/important stored there.
April 29, 2024 at 11:56 am
This was removed by the editor as SPAM
May 3, 2024 at 11:38 am
Does the problem persist when using SQL Native Client instead of the OLEDB?
I've noticed the OLEDB for SQL Server in the wizard does funny things and stopped using it quite a while ago. Of course, I'm still on 2019, so I'm not sure if SQL Native Client is still an available option for 2022.
May 3, 2024 at 8:59 pm
@Brandie Not sure, as I didn't try that option and now the problem is resolved, so there's no way to test.
May 10, 2024 at 4:37 am
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply