Installing SQLServer 2008 R2 on Windows 8.1 Pro

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.
sqlserver_01
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…
sqlserver_02
Fill the databse name, select the database file to restore and which version within the file to restore.
sqlserver_03

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.
black-circuit-512

 

Leave a Reply