Installing SQL Server 2017 Express edition in windows

In this post I'm going to setup SQL Server 2017 Express edition in windows.

Download and Install SQL Server 2017 Express edition

You can download SQL server 2017 express edition on windows official download page. Then you can simply run the exe file and install it. You can select Basic installation as show in bellow and installed in to preferred location in your PC.

Select Basic installation in installation process

Once it successfully installed you can see below screen and on there you can click on install SSMS button and download SQL Server Management Studio (SSMS). Then install it.

Then restart your PC in order to complete the process. After restart you can go to start menu and find "SQL Server Management Studio". Then Open the SQL Server Management Studio application.

Creating new Database in SQL Server 

When you open the SQL Server Management Studio you can see similar to below image and you can log in to SQL Server with windows authentication.

Then Go to database section and lets create sample database by right click on Databases and click on new Database menu.

I'll give DB Name as "TestDB" and click ok. Then it will create database which you can see under Database menu. 

Note : Below section impotent to resolve Login Failed for User (Microsoft SQL Server, Error: 18456)

Login Failed for User (Microsoft SQL Server, Error: 18456)

First lets enable SQL Server authentication by right click on SQLExpress in SQL Server Management Studio and click on properties. Then click on Security tab and click SQL Server and Windows Authentication mode radio button in Server Authentication section. Then click ok. Now users can log in to DB by enter user name and password.

Creating new user in SQL Server 

First of all lets enable "SA" account under Security by go to Logins and right click on sa. Then go to status and make it Enable as show in below.

Then lets create new user by right click on 
Security and go to New->Login.
Then provide Login Name, I will use TestUser and then provide preferred password. Then go to User Mapping and select the database you created as show below.

Then if you try to create new Database connection using SQL Server Authentication it will failed.

First go to Control Panel->Administrative Tools->Services, and search for the SQL Server Agent. Right-click, and select Properties From the Startup Type dropdown, change from Disabled to Automatic. As show in below picture. Then right click on SQL Server Agent (SQLEXPRESS) and click start. Then right click on SQL Server (SQLEXPRESS)  and click restart. After that restart your PC.

Then go to SQL Server 2017 Configuration Manager and click on SQL Server Network configuration section. There you can see TCP/IP and Named Pipes sections are disabled. Right click on it and click Enable. 

Then go to SQL Server Service tab and right click on SQL Server Express. Then click on Restart. 

Now you can open the SQL Server Management Studio and click on Connection Object Explorer and Provide Authentication mode as SQL Server Authentication. Then provide your user name and password. Then click connect you will be able to connect to the database.

Now this user can be used in our applications as well.