OLE/DB provider returned message: Invalid authorization specification

[Eric Spijk]

Hello,

I'm trying to import a table from a MSDE database (databaseB) into a SQL
server database (database A).
Using to following sql statement:

insert tableA
select a.*
from openrowset(sqloledb,'Provider=sqloledb;Password=pwd;User ID=usr;Initial
Catalog=databaseA;Data Source=server', select * from [dbo].[tableB]') as a

I'm getting the following error:

[OLE/DB provider returned message: Invalid authorization specification]
[OLE/DB provider returned message: Invalid connection string attribute]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].

Everything runs fine if I use integrated security!? The used usr/pwd is a
MSDE login account.
A UDL file connection test directly to the MSDE database runs fine.

Thanx!

CHU! Eric

[Sophie Guo [MSFT]]

Hello Eric,

I have reproduced the issue on my side. Based on my research, the following
command works well:


insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','sophietest\msdeinstance';'sa';'password',
'SELECT * FROM test.dbo.tableB ') AS a
GO

or

insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb;UID=sa;PWD=password;Database=test;S
erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as a



Therefore, I recommend you perform the following commands:

1. Make sure the Authentication Mode of MSDE is mixed mode.

The following article is for your reference:

INFO: MSDE Security and Authentication
http://support.microsoft.com/default.aspx?scid=kb;en-us;325022#3


2. Run the following command to test:

insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
'SELECT * FROM databaseA.dbo.tableB ') AS a
GO


Or


insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb;UID=sa;PWD=password;Database=databa
seA;Server=<your MSDE instance name>', 'select * from [dbo].[tableB]') as a


Note:

1. You need to replace the <your MSDE instance name> with your MSDE
instance name.


For more detailed information about OPENROWSET, please refer to the
OPENROWSET topic in SQL Books Online(BOL).


I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Eric Spijk]

Hello Sophie,

Thank you! It works fine now :)

Post by Sophie Guo [MSFT]
Hello Eric,
I have reproduced the issue on my side. Based on my research, the following
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','sophietest\msdeinstance';'sa';'password',
'SELECT * FROM test.dbo.tableB ') AS a
GO
or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb;UID=sa;PWD=password;Database=test;S
erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as a
1. Make sure the Authentication Mode of MSDE is mixed mode.
INFO: MSDE Security and Authentication
http://support.microsoft.com/default.aspx?scid=kb;en-us;325022#3
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
'SELECT * FROM databaseA.dbo.tableB ') AS a
GO
Or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb;UID=sa;PWD=password;Database=databa
seA;Server=<your MSDE instance name>', 'select * from [dbo].[tableB]') as a
1. You need to replace the <your MSDE instance name> with your MSDE
instance name.
For more detailed information about OPENROWSET, please refer to the
OPENROWSET topic in SQL Books Online(BOL).
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.