Solved

Unable to connect SSMS to SQL Server 2012 - Beginner Question

Posted on 2014-02-15
4
403 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 22

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

740 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