Solved

Unable to connect SSMS to SQL Server 2012 - Beginner Question

Posted on 2014-02-15
4
391 Views
Last Modified: 2014-03-12
Hi there

First time user of EE and MS stack.

I have a remote development installation of SQL Server 2012 set up on a remote server.

I know the IP address of the server.

If I am sat on the server then I am able to access the server no problem using Windows Authentication.

However, I wish to access the server from my Windows 7 machine. I have installed SSMS on this machine.

However, upon trying to access the machine I do not get the option for Windows authentication. I have tried accessing it using SQL Server authentication but I am not sure I am doing this correctly.

I have punched a hole in the server firewall for the default port of 1433 (Incoming TCP).

My steps for creating a SQL Server login was to go to SSMS on the server and expand the node Logins under Security under the server name. Not sure if this was the right move?

Please can someone either tell me:

1. How to use Windows Authentication from my remote SSMS
2. How to create a SQL Server authentication on my SQL Server that will work from my remote SSMS



Many thanks



Leon
0
Comment
Question by:LeonPrice
  • 2
4 Comments
 
LVL 19

Expert Comment

by:Patricksr1972
ID: 39861573
Hi Leon,

1) you can only if your machine and the SQL server are in the same domain so it can check your windows credentials. (hence both machine can reach the Domain controllers.
2) When you create a login in SQL you (if you did it correct) gave rights to the databases as well.

Next your SQL server should allow remote administration (in SSMS right click server and select properties)
Next you should be sure SQL is listening to port 1433.

1.In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.

2.In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you want to configure.

3.If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.

4.In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK.

5.In the console pane, click SQL Server Services.

6.In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.
0
 

Author Comment

by:LeonPrice
ID: 39861683
I have tried this but unfortunately it did not work.

However, I think the problem may be two-fold here because I have tried logging on to the server from the local machine and I get the same error.

Therefore I think the problem is down to the way I have the server configured for SQL server authentication (do I need to enable this?)

OR

How I have created the SQL Server login?


Please can you provide steps on the above so that I can rule out this problem.


Thanks


Leon
0
 

Accepted Solution

by:
LeonPrice earned 0 total points
ID: 39861732
Hi there

I solved this problem myself.

It required the properties of the Database server to allow both Windows and SQL Server authentication.

Thanks anyway.


Leon
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now