|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Connection server utilizing trusted connection to other SQL Server
If I command "New connection server" in SQL Server Management Studio, enter the name of the server, choose "Use current security context of application" (the third option, unfortunately I have a German version here) in "Security", and say "OK", I get the following error message: Fehler bei der Anmeldung für den Benutzer 'NT-AUTORITÄT\ANONYMOUS- ANMELDUNG'. (Microsoft SQL Server, Fehler: 18456) This should be something like "Error logging in user 'NT-AUTHORITY \ANONYMOUS-LOGIN'". That's not what I expected, I thought it would establish the connection using my current credentials. What should I do in order do enable that connection? My goal is being able to use the other server like select * from OtherServer.Database.Schema.Table like I can do with our other connection servers (which use a SQL server login instead of a trusted connection). Any help greatly appreciated. Best regards, Michael Michael
I use SQL Server 2005 (SP3) right now, but where you found "Use current security context of application"? You have a login for third party app to connect to SQL Server, right? Is it Windows Login? What is an account SQL Server runs under? "Michael Kremser" <mkrem***@gmail.com> wrote in message If I command "New connection server" in SQL Server Management Studio,news:4f1e0707-d94e-4ab3-9085-79c07f7e9bad@g1g2000yqh.googlegroups.com... Hi NG! enter the name of the server, choose "Use current security context of application" (the third option, unfortunately I have a German version here) in "Security", and say "OK", I get the following error message: Fehler bei der Anmeldung für den Benutzer 'NT-AUTORITÄT\ANONYMOUS- ANMELDUNG'. (Microsoft SQL Server, Fehler: 18456) This should be something like "Error logging in user 'NT-AUTHORITY \ANONYMOUS-LOGIN'". That's not what I expected, I thought it would establish the connection using my current credentials. What should I do in order do enable that connection? My goal is being able to use the other server like select * from OtherServer.Database.Schema.Table like I can do with our other connection servers (which use a SQL server login instead of a trusted connection). Any help greatly appreciated. Best regards, Michael On 7 Jul., 11:51, "Uri Dimant" <u***@iscar.co.il> wrote: Hi Uri!> Michael > I use SQL Server 2005 (SP3) right now, but where you found "Use current > security context of application"? > You have a login for third party app to connect to SQL Server, right? Is it > Windows Login? What is an account SQL Server runs under? As I mentioned, I am not sure about the expression in English (the translations to German are strange sometimes, that makes it difficult to back-translate themn to English). Below the list view I can select out of four options: * Nicht durchgeführt (Don't do) * Nicht in einem Sicherheitskontext verwendet (Not used in a security context) * Im aktuellen Sicherheitskontext der Anwendung verwendet (Used in current security context of application) * In folgendem Sicherheitskontext verwendet: (Used in the following security context) Whereby the last option is followed by two input boxes that allow me to enter a login name and a password. I can connect to both servers using my Windows credentials. So I though that it is possible to create a connection server using the sp_addlinkedserver and - possibly - the sp_addlinkedsrvlogin stored procedures on server A that passes my credentials to server B. Best regards, Michael Michael
Linked server is on picture. What if you change linked server credentials to pass yours, does it work? "Michael Kremser" <mkrem***@gmail.com> wrote in message Hi Uri!news:2fbf24b3-6672-4f1d-b5e3-288177663f26@h11g2000yqb.googlegroups.com... On 7 Jul., 11:51, "Uri Dimant" <u***@iscar.co.il> wrote: > Michael > I use SQL Server 2005 (SP3) right now, but where you found "Use current > security context of application"? > You have a login for third party app to connect to SQL Server, right? Is > it > Windows Login? What is an account SQL Server runs under? As I mentioned, I am not sure about the expression in English (the translations to German are strange sometimes, that makes it difficult to back-translate themn to English). Below the list view I can select out of four options: * Nicht durchgeführt (Don't do) * Nicht in einem Sicherheitskontext verwendet (Not used in a security context) * Im aktuellen Sicherheitskontext der Anwendung verwendet (Used in current security context of application) * In folgendem Sicherheitskontext verwendet: (Used in the following security context) Whereby the last option is followed by two input boxes that allow me to enter a login name and a password. I can connect to both servers using my Windows credentials. So I though that it is possible to create a connection server using the sp_addlinkedserver and - possibly - the sp_addlinkedsrvlogin stored procedures on server A that passes my credentials to server B. Best regards, Michael On 7 Jul., 13:45, "Uri Dimant" <u***@iscar.co.il> wrote: Hi Uri!> Michael > Linked server is on picture. What if you change linked server credentials > to pass yours, does it work? What picture? Have you attached it? May be I can't see it because I use Google Groups... Best regards, Michael No Michael sorry
I meant you have not mentioned before about linked server. Do the both server reside in the same domain? Your login /sql or windows should be give an access to the both servers Show quoteHide quote "Michael Kremser" <mkrem***@gmail.com> wrote in message news:613d874c-dbdc-40e1-bec3-23e3caff97af@p29g2000yqh.googlegroups.com... > On 7 Jul., 13:45, "Uri Dimant" <u***@iscar.co.il> wrote: >> Michael >> Linked server is on picture. What if you change linked server >> credentials >> to pass yours, does it work? > > Hi Uri! > > What picture? Have you attached it? May be I can't see it because I > use Google Groups... > > Best regards, > > Michael Try using the last option and providing username and pasword for login used on the server you are trying to connect to.
On Tue, 7 Jul 2009 04:32:47 -0700 (PDT) Michael Kremser <mkrem***@gmail.com> wrote: Show quoteHide quote > On 7 Jul., 11:51, "Uri Dimant" <u***@iscar.co.il> wrote: > > Michael > > I use SQL Server 2005 (SP3) right now, but where you found "Use current > > security context of application"? > > You have a login for third party app to connect to SQL Server, right? Is it > > Windows Login? What is an account SQL Server runs under? > > Hi Uri! > > As I mentioned, I am not sure about the expression in English (the > translations to German are strange sometimes, that makes it difficult > to back-translate themn to English). Below the list view I can select > out of four options: > > * Nicht durchgeführt (Don't do) > * Nicht in einem Sicherheitskontext verwendet (Not used in a security > context) > * Im aktuellen Sicherheitskontext der Anwendung verwendet (Used in > current security context of application) > * In folgendem Sicherheitskontext verwendet: (Used in the following > security context) > > Whereby the last option is followed by two input boxes that allow me > to enter a login name and a password. > > I can connect to both servers using my Windows credentials. So I > though that it is possible to create a connection server using the > sp_addlinkedserver and - possibly - the sp_addlinkedsrvlogin stored > procedures on server A that passes my credentials to server B. > > Best regards, > > Michael -- grbihno <grbi***@gmail.com> On 7 Jul., 14:06, grbihno <grbi***@gmail.com> wrote:
> Try using the last option and providing username and pasword for login used on the server you are trying to connect to. That causes also an error:> Fehler bei der Anmeldung für den Benutzer 'Domain\User' I also tried without the domain name, but that also did not work. Best regards, Michael First check if you have 'remote access' configured
using sp_configure If you do.. If your server uses mixed mode for logins you colud create a login and try connecting using that login. (It's not recomended, but for now, for troubleshooting) On Tue, 7 Jul 2009 05:29:49 -0700 (PDT) Michael Kremser <mkrem***@gmail.com> wrote: Show quoteHide quote > On 7 Jul., 14:06, grbihno <grbi***@gmail.com> wrote: > > Try using the last option and providing username and pasword for login used on the server you are trying to connect to. > > > > That causes also an error: > > Fehler bei der Anmeldung für den Benutzer 'Domain\User' > > I also tried without the domain name, but that also did not work. > > Best regards, > > Michael -- grbihno <grbi***@gmail.com> Michael Kremser (mkrem***@gmail.com) writes:
> I can connect to both servers using my Windows credentials. So I Linked servers is always a mess. It does not really help to use a GUI> though that it is possible to create a connection server using the > sp_addlinkedserver and - possibly - the sp_addlinkedsrvlogin stored > procedures on server A that passes my credentials to server B. that you need to guess. Use sp_addlinkedserver and sp_addlinkedsrvlogin and read Books Online. Keep in mind that if you specify a username/password, then that must be an SQL login. You can never specify a Windows login/password when you log into SQL Server. But you could try the second parameter @useself of sp_addlinksrvlogin to 'true'. Even if you can login to both servers directly, it may not always work with a linked server, because too many network hops. In the end, setting up an SQL Server login maybe easier. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  
Other interesting topics
RESTORING
drop table not showing up in the transaction log deleting DB strange BLOB beahaviour AWE on SQL Server2005 ID large queries while they execute and kill 'Generate scripts' functionality in SQL 2005 not being consistent Problem with SUM - Help please Backups & Transaction Files How best to move large databases? |
|||||||||||||||||||||||
