Solved

Unable to connect SSMS to SQL Server 2012 - Beginner Question

Posted on 2014-02-15
4
395 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
datetime in sql 6 31
MSSQL: Substring and Charindex error 7 20
MSDN Licensing query 5 59
T-SQL: Eliminating Records Based on Criteria 1 14
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

863 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

25 Experts available now in Live!

Get 1:1 Help Now