Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Unable to connect SSMS to SQL Server 2012 - Beginner Question

Posted on 2014-02-15
4
Medium Priority
?
415 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
3 Comments
 
LVL 23

Expert Comment

by:Patrick Bogers
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

783 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