OLE/DB provider returned message: Invalid authorization specification

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;P
assword=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! EricHello 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\msdein
stance';'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/defaul...;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.|||Hello Sophie,
Thank you! It works fine now
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:n2c8bveKFHA.2876@.TK2MSFTNGXA02.phx.gbl...
> 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\msdein
stance';'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/defaul...;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=data
ba
> seA;Server=<your MSDE instance name>', 'select * from [dbo].[table
B]') 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.
>
>