The error message says it all. When you type in a user name and a password, it's for a SQL authenticated account. The error message is telling you that you entered in Windows credentials, which aren't SQL credentials. Sign up to join this community. The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Learn more. Linked server issue. Can't use windows authentication as remote login account Ask Question.
Asked 9 years, 3 months ago. Active 9 years, 3 months ago. Viewed 23k times. As far as I know, the trick you posted on your last line works for local accounts, not for domain ones. Sometimes this little trick does some magic? No, as Sebastian noted this works only for local accounts, but it still works for local accounts on domain-joined servers.
So for SQL Server Windows Authentication between machines in untrusted domains your only option is to maintain matching local accounts on both servers and keep the passwords in sync. So a direct approach in the scenario using a linked server is not an option.
Could others confirm this? I did actually read this thread, but as far as I can make out it relies on Sql Server authentication on the destination server. Which in my case is about to be disabled. My basic question is really if it is possible to define a linked server in domain A that accesses a server in domain B using windows authentication only.
Bearing in mind that there is no trust between domain A and domain B as I understand matters, this fact rules out impersonisation. So the process that in domain A will use the linked server runs under a domain user in domain A that is unknown in domain B.
Can the linked server in domain A provide mapping to a windows authenticated login in domain B? What David and Sebastian said is correct. If you're dead set or must continue to use SSIS there isn't anything you can do. Sign in. United States English. Ask a question. Quick access.
Search related threads. Remove From My Forums. Answered by:. Remote User Use the remote user to map user defined in Local login. Not be made Specify that a connection will not be made for logins not defined in the list. Be made without using a security context Specify that a connection will be made without using a security context for logins not defined in the list. Be made using the login's current security context Specify that a connection will be made using the current security context of the login for logins not defined in the list.
If connected to the local server using Windows Authentication, your Windows credentials will be used to connect to the remote server.
If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case, a login with the exact same name and password must exist on the remote server.
Be made using this security context Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. Collation Compatible Affects Distributed Query execution against linked servers. If this option is set to true, SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence or sort order.
This enables SQL Server to send comparisons on character columns to the provider. If this option is not set, SQL Server always evaluates comparisons on character columns locally. This option should be set only if it is certain that the data source corresponding to the linked server has the same character set and sort order as the local server.
Data Access Enables and disables a linked server for distributed query access. Use Remote Collation Determines whether the collation of a remote column or of a local server will be used.
If true, the collation of remote columns is used for SQL Server data sources, and the collation specified in collation name is used for non-SQL Server data sources.
If false, distributed queries will always use the default collation of the local server, while collation name and the collation of remote columns are ignored. The default is false. Collation Name Specifies the name of the collation used by the remote data source if use remote collation is true and the data source is not a SQL Server data source. The name must be one of the collations supported by SQL Server.
0コメント