These are the steps I took to install MS SQLServer 2008 R2 + SQL Server Management Studio in Windows 8.1 Pro as well as setting it up for remote access (by modifying windows firewall rules), setting up a database user and restoring an existing database.
Downloading SQLServer 2008 R2 and Management Studio
I downloaded both installations from:
https://www.microsoft.com/en-gb/download/details.aspx?id=30438
There are several files there, I downloaded the x64 (64-bit) ones:
Download Summary:
1. SQLEXPR_x64_ENU.exe
2. SQLManagementStudio_x64_ENU.exe
I Installed SQLServer first (1) then the Management Studio (2).
The following guide helps with installation of the Management Studio (2):
https://blogs.msdn.microsoft.com/bethmassi/2011/02/18/step-by-step-installing-sql-server-management-studio-2008-express-after-visual-studio-2010/
Although I had no problems, just follow the onscreen prompts, (make sure you are in the installation bit on the left panel).
Starting the Management Studio and Loggin in
Search for SQL Server Management Studio and start it.
Set the server name to
.\SQLExpress
Authentication to:
Windows Authentication
Click on Connect
Restoring a Database
Once connected (see above) right click on databases > Restore database…
Fill the databse name, select the database file to restore and which version within the file to restore.
Add a user to access the database remotely
Here I followed this tutorial:
https://gearhost.zendesk.com/hc/en-us/articles/200342665-Adding-users-to-MSSQL-using-SQL-Server-Management-Studio
Set the windows firewall to allow remote access to the database
How to configure SQL Express 2012 to accept remote connections
Added inbound rules for
SQL – TCP 1433
SQL – UDP 1434
SQL – sqlservr.exe
SQL – sqlbrowser.exe
Last checks in Management Studio
Now this varies, but I had to do this in order to be able to login remotely to the database.
In Databases > your_database > Security > users > your_user right click > properties:
In the General tab, the user was set to no login and that couldn’t be changed, so I had to delete and recreate the user with the login name I wanted, then it worked.
Also, ensure that the server is set for both Windows login and SQL
Server right click > properties > Security > Use Windows and SQL login
Static IP
Open a command prompt and run ipconfig to ge the ip address for the machine,
assign this as a static ip on your router if desired.
You should be able to login to the database using the host (ip address) database name, username and password for the user set up above.
