Reason: Failed to open the explicitly specified database. 04:53:19.880 Logon Login failed for user ‘GLOBAL\PORTAL01$’.
One of my client was worried about login failed messages which they were seeing in the SQL Server ERRORLOG file. SQL SERVER - Login Failed - Error: 18456, Severity: 14. Message that SQL Server Log File Viewer shows: Login failed for user Error: 18456, Severity: 14, State 38 What it actually means: Failed to … Login failed for user - Error 18456 - Severity 14, State 38. If the users is coming from an application and intern application is connecting to the database using a different login then it would not be possible to know which user connected to database. 
One more important is you can check if a user is complaining that he cannot log into the database, you can easily check the logs to see what is the issue. Using above steps you can easily keep track of all the users logging into your SQL Server. Bingo! All the login attempts along with server name and user Id is there. Now right click Login Audit enable in Step 1 and select view Audit logs. Step 2: Now right click newly created Audit and Select enable → After that Right Click on Server Audit Specification → Select new Server Audit Specification → Provide appropriate name and → from Audit drop down select LoginAudit ( remember we just created it in previous step) and Audit Auction types as “Failed Login Group” and “Successful_Login_Group” and click ok → Right Click on new created group and select enable. Step 1: Connect instance in management studio and under → Security Dropdown → Select Audits → Right Click and select new Audit… → provide Audit name and select location where Audit files will be saved → Click Ok to create Audit. It’s easy to setup and gives accurate results. Method 3: Using SQL Server Auditing: With SQL Server 2008 and subsequent versions you can enable SQL Server auditing to audit login attempts. Keep your login as an active login to server. If you have logged into server to start the trace make sure you do not logoff or the trace will be stopped. Note: To setup this monitoring you don’t have to restart SQL Server Services. Connect to correct SQL Server instance → Change trace file name → Select save to file (You can save it as SQL table by selecting save to table option, provide appropriate database name and Table name) → Select location where you want to put your trace file → Go to tab Event Selection at the top → Check box Show all events → Choose “Audit Login” and “Audit Login Failed” (Refer screenshot below) → Once done click on run. You can open Profiler from SQL Server management Studio → Tools → SQL Server Profiler or you can login to server All programs → SQL Server → Performance Tools → SQL Server Profiler. Method 2: Using SQL Server Trace: You can setup a trace from same server or a different server to audit all the login attempts to SQL Server and save it safely in a file. Xp_readerrorlog 0,1,”AuditLoginTest1″ – Replace AuditLoginTest1 with your login name you want to search. 
You can also read the error log file in Management Studio by using below command. You can even define timeline or a specific computer name from where login request should be coming. If you can closely look at below screenshot I am only searching for login attempts for User ‘AuditLoginTest1’ so are the results. If you want to search for a particular login attempts, you can always search it in error log. Note: An important point to be considered is, for this change to come into effect you need to restart your SQL Server Service. This will audit all the login attempts in Error log. Method 1: Using SQL Server management Studio: Connect instance in Management Studio → Right Click and select server Properties → Security → Under Login Auditing select both failed and Successful logins and click OK. In this article I will go over some of the quick setup that you can do to enable login auditing. It always a good practice as getting login details real time is easy but what if you want to know if you are investigating an issue happened earlier and need to have list of all login attempts during a particular time. I knew this day was coming and thank fully I already setup login auditing on all our SQL Server instances. Solution: One day my manager called me and asked me to get him details of the people who logged into one of instances a week back between 4-5 PM.
0 kommentar(er)
