Setting up SQL Server 2012

This tutorial should work for setting up a brand new SQL Server instance. In this case, I am using SQL Server 2012.
  • If you can't find SQL Server Management Studio, you may need to install it first.
    1. Download SQLManagementStudio_x64_EMU, run setup
    2. During the Feature Selection step, select Management Tools - Basic (or Complete)
    3. After the installation is complete the executable can be located at:
      • C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\ssms.exe
    4. NOTE: Make sure the database supports both Windows authentication and SQL Server Authentication
  • Creating credentials for remote SQL server access
    1. NOTE: If you have the sa user password, you can just use that account to connect remotely
    2. On the SQL Server, sign in as Administrator or sa (If you forgot your sa password, it can be reset)
    3. Once connected to the local DB Engine, go to Object Explorer > SERVERNAME > Security > Logins
    4. Right click on Logins > New Login ...
    5. Under General > enter Login name
    6. Under General > select SQL Server authentication, uncheck Enforce password policy
    7. Under Server Roles > check public, sysadmin
    8. Click OK to save
At this point, you should have properly set up the credentials for remote login. This next section discusses how to set up the server such that it can be accessed remotely. Keep in mind I don't go over my Windows Firewall setup, that'd be on you.
  • On the SQL server, open up Computer Management
  • Expand Services and Applications
  • Expand SQL Server Configuration Manager
  • Select SQL Server Network Configuration
    1. Select Protocols for SERVERNAME
    2. Right click TCP/IP > Enable
    3. Right click > Properties > IP Addresses tab
    4. Note the IP Address (ie. 192.168.1.35)
    5. Enter 1433 for TCP Port (Your firewall needs to open 1433)
    6. Click OK to save
  • Select SQL Server Services, you should see...
    1. SQL Server (SERVERNAME) Running
    2. SQL Server Agent (SERVERNAME) Stopped
    3. SQL Server Browser Running
  • Restart #1 and #3 services listed directly above
At this point you should be good to go. Just 1 last step inputting the correct hostname, username, etc.
  • Server type: Database Engine
  • Server name: 192.168.1.35\SERVERNAME
  • Authentication: SQL Server Authentication
  • Login: Username set up above, or sa
  • Password: Self-explanatory
Done.





Posted by

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)