SQL Error Fix – “Login failed for user” error message when you log on to SQL Server First time after fresh install

Issue

I faced this issue when I installed SQL 2012 and SQL 2014 in an Active Directory configured machine mostly this will be the case for a typical developer machine where you configure all the services in one machine. While installing I did configure the mixed mode authentication with SQL + Windows. Provided the sa account that’s created in the AD. I did add the local administrator as SQL administrator. Installation gave some warning about AD configured on the server as a typical developer, I just ignored and installed, now the installation is complete. I got an error instance didn’t start in a timely manner.After fixing the service issue I tried to connect to the server and there comes the new issue of login failed. I tried with the administrator account and sa that we used while installing no luck and stuck with a service that no one is allowed to use.

8tlgp

Fix

I have mentioned the steps that helped resolved both the issues.

Service issue

To start the service we I have to set the service account.

  • Open the SQL server configuration manager

1

  • Select the instance that you installed and right click try to start the instance
  • If it works great else set the service account and that should resolve it

2

Login issue

  • The login that we are trying was not added or provided access when it installed.
  • We have to start the SQL in single user mode that will allow the local administrator group users to log in to the server, then add the user and provide access.

3

  • Stop the service if its running and add a startup parameter -m or -f to the startup parameter. (Microsoft article link)
  • Start the service try connecting with the local administrator or users from the administrator group
  • Go add the administrator and the sa account grant access. Follow the step by step instruction. (link)
  • Stop the service and remove the startup parameter. Start it again and try login with the sa account voila it works !!

Note: Dont forget to remove the startup param after fixing the issue.

Related errors:

http://dba.stackexchange.com/questions/87390/sql-server-authentication-login-failed

http://blog.sqlauthority.com/2014/01/17/sql-server-fix-login-failed-for-user-username-the-user-is-not-associated-with-a-trusted-sql-server-connection-microsoft-sql-server-error-18452/

SharePoint DB an Overview

The SharePoint heart is SQL it acts as the brain behind the scenes. SharePoint installed on a server edition OS uses the Internal Database by default. You can manually configure SharePoint to pick the SQL server on Configuration. To check the instances running on the machine you can use the SQL Configuration manager.

 image

The above picture shows two instances Internal Database and SQL Express.

Use SQL management studio for easier access. Use the instance to connect to appropriate instance and expand the databases to view the db attached to the particular instance

smgt

The default configured would have three db’s by default

 
_AdminContent  (Central Admin)

    The central administration db will be generally one per farm.

 
Wss_Content (Site Content)

    The content database is created one by default that can hold 15000 sites
This can be created separately for each application created in the farm to have control over the growth of the db.

Find the content db in your farm

Central Admin >>Application management >> Content Databases

 
Wss_Search (Search Index)

This is only applicable if only search index is enabled for the farm.

Note: The default database created will have the following settings auto growth (db would grow as the data increases) and no size limit for the db file. This applies for both the .MDF(SQL Database) and .LDF(SQL Log)

The procedure that can avoid the situations. The following are my personal view and take necessary backup before you proceed.

Pre – Action
  1. Connect to instance with Management studio
  2. Right-Click the db properties

image

3.Select the Files and you can see two files the db and the log

4.Click the button in the Autogrowth button and you will find the following window.

image5. Set the Restricted file growth as plan and that size should be greater than the existing file.

 

Post – Action

The Pre-action would help once a new server is setup. The main case is everyone miss this step and finally once log grows huge they search around for an solution. Check this might help. The below is my view and take precautions before giving a try.

  1. Stop SharePoint services and IIS
  2. Connect to the SQL Instance
  3. Expand the Databases item
  4. select the db right click >>Tasks >> Detach
  5. Rename the log file of the detached db
  6. Attach the db manually using the following query in SQKlMGmt
  7. sp_attach_single_file_db @dbname= ‘databasename’, @physname= ‘C:\Databases\databasefile.mdf’

7. Start SharePoint services and IIS that generates a new log file.