Openrowset error 18452 invalid connection string

[jubble]

Is it possible to use OpenRowSet to move data to/from Servers that use different authentication methods?

If I use Query Analyzer to connect to a Server using WINDOWS AUTHENTICATION and run the following query that attempts to connect to a database that uses MIXED MODE AUTHENTICATION I get the error ...

Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.
[OLE/DB provider returned message: Invalid connection string attribute]

The query is....
INSERT INTO AccountType SELECT a.* FROM OPENROWSET('SQLOLEDB', 'Data Source=dsvr;Initial Catalog=hughjunktest;uid=sa;pwd=;Use Encryption For Data=False;Persist Security Info=False;Packet Size=4096;', 'SELECT * FROM hughjunktest.dbo.AccountType') as a

This query works fine when connecting to a MIXED MODE AUTHENTICATION Server but not a WINDOWS AUTHENTICATION Server.

Is there another parameter I need to include in the connection string in the OPENROWSET method?
Maybe a service pack?!?
Prayers?

Please tell me it can be done......

 

[vongrunt]

Go to

http://www.connectionstrings.com,

check SQL Server/OLEDB branch, trusted connection (hint: SSPI).

 

[jubble]

Sorry, I didn't explain the problem too well.

At the moment I am using Query Analyzer logged on to a SQL Server via Windows Authentication.

The OpenRowSet query is attempting to make a connection to a server using Mixed Mode Authentication in an effort to pass data to a Windows Authentication Sql Server (the one currently logged on to).

Running the query results in the error above so I am wondering if Sql Server actually allows one to use both Authentication methods in the same process (or execution).

As far as I know putting the parameter 'Integrated Security=SSPI' is exactly the same as 'Trusted_Connection=Yes' which does not apply for the connection string within the query above because it needs to connect using Mixed Mode Authentication.

Perhaps there is an extra parameter required to tell SQL Server to bypass the Windows Authentication check which it is probably doing by default because I am currently logged into a Windows Authentication Sql Server?

Just as a note... if I use WA->WA or MM->MM (adjusting the connection query accordingly) the query works, but if I try and mix authentication it won't.

Regards, Hugh

 

[JamesLean]

In your original question, it looks like the server you are trying to connect to thru OPENROWSET is not set up for mixed-mode authentication, therefore you can't login using the sa login.

I have tested this with the following setup and it works fine:

ServerA - mixed mode
ServerB - mixed mode

1) Login to ServerA using windows authentication.
2) Execute the following query, using mixed mode authentication to connect to ServerB:

SELECT * FROM OPENROWSET('SQLOLEDB', 'Data Source=ServerB;UID=sa;PWD=mypassword', 'SELECT * FROM mydb.dbo.mytable')

The only thing I can't check is if I change ServerA to windows only and do the same thing, but I really can't see that that will have any effect on it.

--James

 

[SQLSister]

And incidentally sa and no password is a very bad way to connect. sa should always have a password and no outside connection should be using sa. You don't need admin rights for this type of query. This is just opening yourself up to be hacked into.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief