MSDE 2005 Login 서버 인증

Published Feb 18, 2008 | Updated Sep 22, 2022 | 0 comments

VS.Net 설치시 MSDE2005 가 설치된 상태

sa 계정으로 SQL Server 모드로 로그인을 했으나 실패

사용자 ‘sa’이(가) 로그인하지 못했습니다. 이 사용자는 트러스트된 SQL Server 연결과 관련되어 있지 않습니다. (Microsoft SQL Server, 오류: 18452)

도움말을 보려면 다음을 클릭하십시오: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476

윈도우 인증으로 로그인 후 sa 계정 비번 변경후 다시 로그인 해도 실패 – 같은 메세지

다른 사용자 계정을 만들어서 로그인 해도 같은 경우…

SQL Server 모드로 로그인만 하면 무조건 같은 에러 ㅠㅠ

그래서 위 도움말 링크를 찾아가 보았다

Details
Product:	SQL Server
ID:	18452
Source:	MSSQLServer
Version:	8.0
Component:	SQL Engine
Message:	Login failed for user ‘%ls’. Reason: Not associated with a trusted SQL Server connection.

Explanation
If the user listed in the message is a SQL account name, this message generally indicates that this SQL Server instance is configured to only accept Windows-authenticated connections (Windows Authentication mode), but the connection attempt was made with a SQL Server-authenticated login. If the user listed in the message is NULL, there was a problem with the Windows Authentication passed to SQL Server. The problem could be due to many factors, including incorrect name resolution, an out of sync domain controller, no available domain controllers, SSPI problems, or policies or permissions settings that prohibit connections. If the error occurs when one computer is attempting to delegate Windows-authenticated permissions to another server, this error may indicate that the Windows delegation, often called a double-hop, is not properly configured within the domain. A common example of a double-hop is a linked server query where the client connects to an instance of SQL Server, and that instance tries to delegate the Windows permissions to an instance of SQL Server on another computer.

User Action
If the user listed in the message is a SQL account name, you must either: Reconfigure the SQL Server instance to Mixed Mode (Windows Authentication and SQL Server Authentication). Connect to the SQL Server instance using only Windows-authenticated logins. For more information about Windows Authentication, see Microsoft Knowledge Base article 269587. If the user listed in the message is NULL: Verify that name resolution is working correctly. From a command prompt at the client, ping the server by name and verify that it returns the correct IP address: PING {ServerName} From a command prompt at the client, ping the server by IP address, and verify that it returns the correct server name: PING -a {ServerIP} From a command prompt at the server, ping the client by name and verify that it returns the correct IP address: PING {ClientName} From a command prompt at the server, ping the client by IP address and verify that it returns the correct client name: PING -a {ServerIP} If the client machine, the server machine, and the account used to log on to the client are not all in the same domain, verify that the trusts between the domains are properly configured and working. Verify that the Logonserver used by the client is a valid domain controller. Open a command prompt on the client machine Type “SET logonserver” (without the quotes) and press Enter. The server name returned should be a valid domain controller. Type “dir \ValueReturned etlogon” and press Enter. ValueReturned = the value from the SET statement above. The directory listing should not return an error. If it does, troubleshoot that error. For more information about cached credentials, see Microsoft Knowledge Base article 242536. Check your local security policies to see if any essential rights have been denied. Try to connect to a share on the server. If connecting to a share fails, the account may not have “access this computer from the network” rights or may be missing other domain or network level permissions. If your domain is configured to use Kerberos, you could be experiencing some sort of SSPI error. For more information about SSPI in general and how to troubleshoot a “Cannot Generate SSPI Context” error, see Microsoft Knowledge Base article 811889.Additional information gathering steps that can help to narrow down the problem: Are there any messages in any of the event viewer logs on the server, client, or domain controller around the same time that this error occurs? Does the problem only occur from certain tools? Try to connect from your application, Query Analyzer, OSQL, and an ODBC DSN or OLE DB UDL. Does the problem only occur for certain protocols? You can test with various protocols by specifying the protocol in your connection string. For more information about how to use the server name parameter in a connection string to specify the client network library, see the Microsoft Knowledge Base article 313295. Does the problem occur locally or only from one or more remote computers? If it only happens on some computers, are there differences such as MDAC version, domain membership, router path, and domain controller used for validation? If the problem occurs during Windows delegation (double-hop), verify that the domain and Kerberos are configured to allow double-hops.


Version:	10.0
Component:	SQLEngine
Symbolic Name:	LOGON_INVALID_CONNECT
Message:	Login failed for user ‘%.ls’. The login is a SQL Server login and cannot be used with Windows authentication.%.ls

Explanation
The user attempted to login with credentials that cannot be validated. Possible causes are: The login may be a SQL Server login but the server only accepts Windows Authentication. You are trying to connect using SQL Server Authentication but the login used does not exist on SQL Server. The login may use Windows Authentication but the login is an unrecognized Windows principal. An unrecognized Windows principal means that the login cannot be verified by Windows. This could be because the Windows login is from an untrusted domain. Similar problems can cause the less-specific error 18456.

User Action
If you are trying to connect using SQL Server Authentication, verify that SQL Server is configured in Mixed Authentication Mode. If you are trying to connect using SQL Server Authentication, verify that the SQL Server login exists. If you are trying to connect using Windows Authentication, verify that you are properly logged into the correct domain.

위와 같은 페이지 나옴

여기서 마지막 부분

User Action

If you are trying to connect using SQL Server Authentication, verify that SQL Server is configured in Mixed Authentication Mode.

그래서 서버 속성에 들어가 보니
보안 탭에 서버 인증 부분이 Windows 인증 모드 로만 되어있다 ㅡㅡ;;

일반적으로 그냥 직접 설치할 때는 설치시 습관적으로 sa 계정으로 로그인 하게끔 비번 설정하게 하던게 버릇이 되어서 서버 속성에 저런게 있는 줄 몰랐음

그래서 SQL Server 및 Windows 인증 모드 로 변경하고 다시 로그인 하니깐 제대로 됨 (‘_’)

고로, 서버속성에 저런게 있다는 거 진작 알았으면

오늘 같은 삽질은 없었을 터…..

혹시나 나중에 또 이런 일이 있게 하지를 말자..

← Prev Next →

TAG INFORMATION

Learn more on this topic

ClickOnce 업데이트 서버 구성 방법 정리

Published Jul 30, 2008 | Updated Sep 22, 2022 | .Net, Tool | 0 Comments

1. IIS 설치 후 FrontPage Server Extensions 2002 구성 구성이 되면 아래와 같이 별도 탭이 생성됨 2. IIS 셋팅 - 인증 3. IIS 셋팅 - HTTP 헤더 설정 콘텐츠 만료 지정 하고 즉시 만료로 하면 업데이트시 캐쉬를 신경쓰지 않아도 됨 사용자 지정 HTTP 헤더 확인 – 없으면 추가 4. IIS 셋팅 - ASP.NET 설정 현재 개발 프레임웍에 맞게끔 설정이 되어있는지 확인 MIME 형식 연결 MIME 설정은...