How to setup and use a SQL Server alias
Issue:
I have an application that has a specified database connection that I either can't or don't want to change. However, I need to move the database to a new server. How can I do this easily without breaking other things that maybe using this connection and/or database?
Resolution:By using an alias, we can keep the application configuration the same, but tell the operating system to look somewhere else for the database. One proviso is that once an alias is created, it is set at the operating system level. That means if we have two applications which connect to MySQLServer on that computer, once the alias is created, both will be re-pointed based on the alias. You can create an alias using one of two utilities:
1. SQL Server Configuration Manager
2. SQL Server Client Network Utility
Events:
Event Type: ErrorEvent Source: MSSQL$EV
Event Category: (4)
Event ID: 17806
Date: 1/30/2009
Time: 7:47:19 PM
User: N/A
Computer: LAB9CUST4
Description:
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 172.22.3.56]
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.Data:0000: 8e 45 00 00 14 00 00 00 ŽE......0008: 0d 00 00 00 4c 00 41 00 ....L.A.0010: 42 00 39 00 43 00 55 00 B.9.C.U.0018: 53 00 54 00 34 00 5c 00 S.T.4.\.0020: 45 00 56 00 00 00 00 00 E.V.....0028: 00 00 ..
Event Type: Failure Audit
Event Source: MSSQL$EV
Event Category: (4)
Event ID: 18452
Date: 1/30/2009
Time: 7:47:19 PM
User: N/A
Computer: LAB9CUST4
Description:
Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 172.22.3.56]
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.Data:0000: 14 48 00 00 0e 00 00 00 .H......0008: 0d 00 00 00 4c 00 41 00 ....L.A.0010: 42 00 39 00 43 00 55 00 B.9.C.U.0018: 53 00 54 00 34 00 5c 00 S.T.4.\.0020: 45 00 56 00 00 00 07 00 E.V.....0028: 00 00 6d 00 61 00 73 00 ..m.a.s.0030: 74 00 65 00 72 00 00 00 t.e.r...
See below for reference:
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=84680&highlight_key=y&keyword1=sspi
http://blogs.msdn.com/sql_protocols/archive/2008/05/03/understanding-the-error-message-login-failed-for-user-the-user-is-not-associated-with-a-trusted-sql-server-connection.aspx
More info while connecting with named instance:
TITLE: Connect to Server ------------------------------ Cannot connect to EVDB.------------------------------
ADDITIONAL INFORMATION:
Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------no error in evnt log
Resolution:
See http://www.mssqltips.com/tip.asp?tip=1620
----------------------------------------------------------------------------------------
