Can I use a domain account in OPENDATASOURCE()-CodePudding

I have been trying and failing to use a domain account in SQL Server's OPENDATASOURCE().

I've tried many variations, including the following:

OPENDATASOURCE ('SQLNCLI', 'Data Source=<server name>; User ID=<domain>\<user>; Password=<password>' )

I've also tried escaping the backslash like this:

OPENDATASOURCE ('SQLNCLI', 'Data Source=<server name>; User ID=<domain>\\<user>; Password=<password>' )

I get following error message in the first case:

Msg 18456, Level 14, State 1, Line 1
Login failed for user '<domain>\<user>'

In the second case, I get the same except that I see both backslashes in the error message.

Is what I'm trying to do possible? Also, the password includes a backtick (`) character. Is that forbidden in OPENDATASOURCE?

CodePudding user response：

EDIT: To use a backtick or special char in the password you need to encapsulate in double quotes

For example, if your password is "`password", you may need to enter like:

OPENDATASOURCE('SQLNCLI', 'Data Source=server_name;uid=domain\username;pwd="`password"')

CodePudding user response：

No OleDb drivers support using Windows Auth with provided username/password. The only way to use Windows Auth is by impersonating the user, injecting the user's credentials in the Windows Credential Store, or using runas /netonly.