Tips on installing MS SQL 2012 Express edition

14/10/2013 Leoš Literák

Well, I was once again forced to use another Microsoft product – their database this time. And it took me several hours to make it work, so I want to share some details in hope, that it will be helpfull to you.

The installation was smooth but later I realized that I cannot contact this database from java. Why? TCP IP was disabled by default. This is very secure option, yet I can imagine more secure one – switch the computer off. Stop kidding. Open SQL Server Configuration Manager, open Network configuration from left tree and select Protocols node. There you need to enable TCP/IP protocol, open its properties dialog and change the property Enabled to true.

Dis is one half. Oh no, it is back! (Who does understand this joke?)

If you try to connect this new database, you will fail again. MS SQL will play a game Guess my port with you. Ok, they have some naming service and some good reasons for this behaviour, but let keep things simple and switch off dynamic ports feature. Choose IP addresses tab. Either modify one concrete network interface or find the last section IP All. Delete value in the option TCP Dynamic Ports and set 1433 to the option TCP Port. Finally you have to restart MS SQL service.

Stop, we are not finished yet. You do not want to use your windows credentials and probably store them somewhere in configuration files, do you? So it is time to switch off Windows Authentication mode. Open MS SQL Management Studio, edit server properties, Security page particularly. There you need to select mixed mode – both SQL Server and Windows Authentication modes in Server authentication section.