Tips on installing MS SQL 2012 Express edition

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.

TCP/IP protocol properties

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.

Default value with dynamic portupdated value to MS SQL default portRestarting 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.

Selecting authentication mode

And finally create some user with appropriate roles. It is neccessary to be DDL admin and DB owner for development purposes. Restart MS SQL

Adding new user

Some useful links I used:

Blog bitcoin address: 1GQBYqZwiHT72UrLCCSv4j6WkK65FjTPJk

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *

Tato stránka používá Akismet k omezení spamu. Podívejte se, jak vaše data z komentářů zpracováváme..