Install reconfiguration/setup wizard cannot connect to local or remote MSSQL database

Symptoms
Error:

- Login failed
- Failed to connect to server

 

Common causes include: - Service not started or has not been restarted after install. - Incorrect port configuration. - MSSQL incorrectly configured. -Client tools not installed.

Has the SQL Server services been restarted since install?
Checking MSSQL service:

1. Start run
2. Type "services.msc"
3. In the services window look for "SQL Server"
4. If it’s not started start the service.
5. If it has not been restarted since install right click on the service and select restart.

Check MSSQL port configuration:

1. Start\All programs\Microsoft SQL Server 2005\Configuration Tools\"SQL Server Configuration Manager".
2. Expanded "Protocols for MSSQLSERVER".
3. Under Protocol Name double click TCP/IP.
4. Ensure that Enabled is set to Yes.
5. Click IP Addresses tab.
6. Verify network interfaces (IP1 - IP#) active set to yes.
7. Note down TCP port value (by default it is 1433).
8. If changes have been made restart the SQL Server service.

Check network connection to MSSQL server?

1. Start run.
2. Type "cmd"
3. Click OK.
4. In command prompt window. Type "telnet <ip address> <port>" where <ip address> is ip address of the MSSQL server and <port> is the MSSQL server port found in "SQL Server Configuration Manager"
   Example command: telnet 192.168.1.10 1433
5. If the screen goes blank and the curser is flashing in the top left connection has been made.
6. If telnet fails this indicates port may not be open or unable to reach the IP address.

Check is MSSQL port is open on SQL server:

1. Start run
2. Type "cmd"
3. Click ok.
4. In command prompt window type netstat -aon | find /i ":<MSSQL port>" where <MSSQL port> is the SQL servers port found in "SQL Server Configuration Manager"
   Example command: netstat -aon | find /i ":1433"
5. If results return with a listing of addresses with source and/or destination ports using the SQL server port then the port is open and in use.
6. If result after command is blank the port is not open. Check port configuration and services.

Is MSSQL Client Tools installed?
If SEPM is installing to a remote MSSQL database the SEPM server will need client tools installed

Installing MSSQL Client tools:

To install client tools only for SQL Server 2005

1. Insert installation disk and start the installation wizard..
2. On the Components to install dialog during the setup select the option: Workstation tools, Books Online and development tools.
3. Click on the "Advanced" tab at the bottom.
4. Under the Client Components node, select the client tools.
5. Click next to complete install.

To install client tools only for SQL Server 2000

1. Insert installation disk and start the installation wizard.
2. Select SQL Server 2000 Components, select Install Database Server, and then click Next at the Welcome screen of the SQL Server Installation Wizard.
3. In Computer Name dialog box, Local Computer is the default option, and the local computer name appears in the edit box. Click Next.
4. In the Installation Selection dialog box, click Create a new instance of SQL Server, or install Client Tools, and then click Next.
5. Follow the directions on the User Information, Software License Agreement, and related screens.
6. In the Installation Definition dialog box, click Client tools only, and then click Next.
7. In the Select Components dialog box, accept the defaults or select the components you want, and then click Next.
   You can select an item in the Components list, such as Management Tools, and then select items from the related Sub-Components list, such as Enterprise Manager. Click to select items you want to install; clear the check box of the items you do not want to install.
8. In the Start Copying Files dialog box, click Next to complete the installation of the client tools.

Verify MSSQL user account authenticate to the MSSQL server database instance:

1. Start run
2. Type "cmd" click ok
3. In command prompt type "osql -U sa -P <password>" where <password> is user account sa's password.
   Example command: osql -U sa -P password
4. If prompts changes to "1> " then authentication details are correct and connection has been made.
5. Type "quit" to exit osql.

To Verify the account that SEPM uses to access the SEPM database perform the following

1. Start run
2. Type "cmd" click ok
3. Type "osql -U <user-account> -P <password> -d <database-name>
    <user-account> is the account configured to connect to the SEPM database during install.
    <password> is the password for the account used in the command.
    <database-name> is the name of the database connecting to.
   Below is an example using default details.
   Example command: osql -U sem5 -P password -d sem5
4. Again If prompts changes to "1> " then authentication details are correct and connection has been made.
5. Type "quit" to exit osql.

Note: If the above osql commands failed with "Login Failed for user 'sem5'" the password enter maybe incorrect and may require reset.

How to reset MSSQL user account password:

1. Start\Programs\Microsoft SQL Server\SQL Server Enterprise Manager.
   Note for MSSQL 2005 go to Start\Programs\Microsoft SQL Server 2005\SQL Server Management Studio
2. Connect to Server window using "Windows Authentication"
3. Navigate to the Logins object under the Security folder on the SQL Server you wish to administer.
4. Right click on the user account you wish to change the password for and select Properties or simply double click on it.
5. A new properties window will open. Enter a new password in the Password field under the Authentication options.
6. Click OK.