LeonPrice
asked on
Unable to connect SSMS to SQL Server 2012 - Beginner Question
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.