muk avatar image
muk asked

please help login failed

Hi all, I am having a problem with one of my databases. In the database I have a view written that gets data from another database on another server. Everytime I try to do anything with that view like select or alter it gives me this error: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. even when I tried to login as "sa" it gave me the error login failed for sa..... what am I doing wrong? Here is my query: USE [MyLaw10] GO /****** Object: View [dbo].[Import_Student_view] Script Date: 06/12/2012 16:03:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[Import_Student_view] AS SELECT DISTINCT TOP (100) PERCENT STUDENT_ID AS StudentUPI, ENROLL_CURRENT_STATUS AS EnrollmentStatus, STUDENT_LAST_NAME AS Lname, STUDENT_FIRST_NAME AS Fname, STUDENT_MIDDLE_NAME AS Mname, STUDENT_ID + '.jpg' AS image FROM DTSQLPROD01.coll18_production.dbo.STUDENT_ENROLLMENT_DETAIL_VIEW AS STUDENT_ENROLLMENT_DETAIL_VIEW_1 WHERE (ENROLL_TERM = '2012SP') GO Thank you in advance for any assistance!!!!!!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Pavel Pawlowski avatar image
Pavel Pawlowski answered
So, you are using a linked server in the query. Then there are several possibilities: You have SQL Server 2005 and you are affected by problem mentioned in the KB925001 - [FIX: Error message when you run queries between two SQL Server 2005 linked servers: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"][1] Second option is that in the Security options of the Linked Server there is selected option that **`Connection will be made without security context`**. And the third possibility is that you are connecting to a Linked Server which is not on the same machine as your SQL Server instance and have security options se to **`Be made using the login's current security context`**, but you have not properly configured Kerberos to be able to pass the security tokens to the remote machine. So you have a few possibilities to solve this: 1. Map logins to remote logins in the Security tab of Linked Server 2. Do the connection using a predefined security context of remote user. 3. Use the current login's security context and setup the Kerberos authentication correctly. If the linked server is SQL Server, you can take a look on some articles related to Kerberos authentication: Techned: [Using Kerberos Authentication with SQL Server][2] SqlServerCentral: [Configuring Kerberos Authentication][3] MSDN: [Kerberos Authentication and SQL Server][4] [How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005][5] [1]: [2]: [3]: [4]: [5]:
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.