How to connect to local instance of SQL Server 2008 Express

Please check the ServerName which you provided. It should match with the below shown Name in the UserName textbox, and that name should followed with \SQLEXPRESS:

https://i.stack.imgur.com/6Dqbk.png

---

Start your Local SQL Server Service

Start SQL Config Manager: Click Start -> Microsoft SQL Server 2008 R2 -> SQL Server Configuration Manager

Start SQL Services: Set the SQL Server (SQLEXPRESS) and SQL Server Browser services to automatic start mode. Right-click each service -> Properties -> Go into the Service Tab

This will ensure they start up again if you restart your computer. Please check to ensure the state is "Running" for both services.

https://i.stack.imgur.com/lO2tA.jpg

Login and authenticate with your Local SQL Server

Now open up SQL Server Management Studio and click "Connect to Object Explorer" and select Server Name:

[Your PC name]\SQLEXPRESS

Example: 8540P-KL\SQLEXPRESS or (localhost)\SQLEXPRESS

To find your PC name: Right click My Computer -> Properties -> Computer Name tab

Alternative: Login using windows authentication: Using the user name [Your Domain]/[Your User Name]

https://i.stack.imgur.com/Bptt6.png

Setup User Account

Create a new Login acct: In SQL Mgmt Studio -> Expand your local Server -> Security -> Right click on Logins -> New Login

Set Password settings on New User Account: Uncheck Enforce password policy, password expiration and user must change pw(Since this is local) Default database -> Your Database

Grant roles to New User Account: User Mapping Page -> Map to your db and grant db_owner role Status Page -> Grant Permission to connect and Enable Login

https://i.stack.imgur.com/jBo4B.png

Setup Access Permissions/Settings for User

Enable all auth modes: Right click your Local Server -> Properties -> Security Tab -> Enable SQL Server and Windows Authentication Mode

Enable TCP/IP: Open SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for SQLEXPRESS -> Enable TCP/IP

Restart SQL Server Service: You will have to restart the SQL Server(SQLEXPRESS) after enabling TCP/IP

https://i.stack.imgur.com/rlmgE.png

Database Properties File for Spring Project

database.url=jdbc:jtds:sqlserver://[local PC Computer name];instance=SQLEXPRESS;DatabaseName=[db name];

database.username=[Your user name] database.password=[Your password]

database.driverClassName=net.sourceforge.jtds.jdbc.Driver

If you want to view larger screen shots and better formatting of the answer with more details please view the blog article below: Setting up a Local Instance of SQL Server 2008 Blog Post:

---

I used (LocalDB)\MSSQLLocalDB as the server name, I was then able to see all the local databases.

---

Haha, oh boy, I figured it out. Somehow, someway, I did not install the Database Engine when I installed SQL Server 2008. I have no idea how I missed that, but that's what happened.

---

I know this question is old, but in case it helps anyone make sure the SQL Server Browser is running in the Services MSC. I installed SQL Server Express 2008 R2 and the SQL Server Browser Service was set to Disabled.

Start->Run->Services.msc Find "SQL Server Browser"->Right Click->Properties Set Startup Type to Automatic->Click Apply Retry your connection.

---

I've just solved a problem related to this which may help other people.

Initially when loading up MSSMSE it had the server as PC_NAME\SQLEXPRESS and when I tried to connect it gave me Error: 26 - Error Locating Server/Instance Specified, so I went into SQL Server Configuration Manager to check if my SQL Server Browser and SQL Server services were running and set to automatic, only to find that instead of saying SQL Server (SQLEXPRESS) it says SQL Server(MSSQLSERVER).

I then tried connecting to PC-NAME\MSSQLSERVER and this time got SQL Network Interfaces, error: 25 - Connection string is not valid) (MicrosoftSQL Server, Error: 87) The parameter is incorrect so I googled this error and found that somebody had suggested that instead of using PC-NAME\MSSQLSERVER just use PC-NAME as the Server Name at the server connection interface, and this seems to work.

There's a link here http://learningsqlserver.wordpress.com/2011/01/21/what-version-of-sql-server-do-i-have/ which explains that MSSQLSERVER is the default instance and can be connected to by using just your hostname.

I think this may have arisen because I've had SQL Server 2008 installed at some point in the past.

---

Under Configuration Manager and Network Configuration and Protocols for your instance is TCP/IP Enabled? That could be the problem.

---

var.connectionstring = "server=localhost; database=dbname; integrated security=yes"

or

var.connectionstring = "server=localhost; database=dbname; login=yourlogin; pwd=yourpass"

---

For me, I was only able to get it to work by using "." in the server name field; was banging away for awhile trying different combos of the user name and server name. Note that during install of the server (ie this file: SQLEXPR_x64_ENU.exe) i checked default instance which defaults the name to MSSQLSERVER; the above high voted answers might be best used for separate named (ie when you need more than 1) server instances.

both of these videos helped me out:

use dot for server name: https://www.youtube.com/watch?v=DLrxFXXeLFk

general setup: https://www.youtube.com/watch?v=vng0P8Gfx2g

---

One of the first things that you should check is that the SQL Server (MSSQLSERVER) is started. You can go to the Services Console (services.msc) and look for SQL Server (MSSQLSERVER) to see that it is started. If not, then start the service.

You could also do this through an elevated command prompt by typing net start mssqlserver.

---

For me it was a windows firewall issue. Allow incoming connections. Opening port didn't work but allow programs did.

Link

Link2

---