Solved

Connecting SQL 2014 Management Studio to SQL Server 2014 Express

Posted on 2014-12-11
16
374 Views
Last Modified: 2015-01-15
I'm very much an SQL noob, so I may not have all the details in my question (though I'll try).

I've been asked to assist a SQL consultant in getting a database uploaded to central location where he can show the database to others.

So far, I've created a virtual machine running Windows 2008 R2, and I've installed SQL Server Express 2014 on this virtual machine.  No other software is on the virtual server.  I've disabled the firewall on the virtual server.  I've enabled dynamic TCP/IP port connections.  I can telnet to the server (10.10.10.46) on port 49205 and get a connection.  I just really don't know what I'm looking for to enable this and it seems neither does the consultant.  

I think I'd just like some assistance with best practices.  Per my consultant, all he needs to be able to do is connect to the SQL server using SQL management studio and he'll do some query analyzer thing.

So let's say you install SQL Server Express 2014 on a brand new VM running Win 2008 R2.  What changes do you make to make sure that users on the LAN can connect to the SQL Server 2014 Express using Windows authentication (I configured the server to use Windows authentication when I installed SQL Server).

Thank you
0
Comment
Question by:dmessman
[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
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40495155
Any idea how your consultant is connecting to the server. Is he going to use VPN to connect to the network . Your consultant can connect to your database server thru either after remote accessing to the server or after establishing a connection from his local machine  thru VPN and using SSMS from his machine
0
 
LVL 3

Expert Comment

by:Richard Obenchain
ID: 40495176
Default ports for SQL are 1433 and 1434.  As long as those are open and accepting from the outside, the server itself should be accessible through SSMS.  Authentication and such is handled within the server.

If you're installing Express, I believe it defaults to Named Instance.  If it's the only DB you want on there, it's easier to put it to Default Instance.  The difference is in how it's referenced ("servernave" vs "servername/instancename"), and is minor, but it might throw someone off if they're not expecting it.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40495824
can you tell us more about how and where (as opposed to your LAN) the consultant needs to connect.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 9

Author Comment

by:dmessman
ID: 40496321
Thanks all.  I think Richard is on the right track here.  My server isn't responding on port 1433 or 1434.  My firewall is currently disabled, so it's not a firewall issue.  Plus, I can't connect via "telnet 10.10.10.46 1433" or "telnet 127.0.0.1 1433" from the server itself.

This looks pretty simple.  I see the client protocols sections of things, and I enabled everything for port 1433 (see screen shots below).  Is there any kind of gotcha here anywhere?  Or does everything look right and things are just messed up somewhere and I should start over (which wouldn't be too hard since an empty VM).

SQL 2014 server express settings 1
SQL 2014 server express settings 2
SQL 2014 server express settings 3
SQL 2014 server express settings 4
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40496350
Looking at the configuration and your actions, the instance name is not resolved by TCPIP.  Please have an DNS alias created on the AD and start from there.

Hope this helps.
0
 
LVL 9

Author Comment

by:dmessman
ID: 40496397
You said "the instance name is not resolved by TCPIP."

I'm not sure what means.  I can easily create an A record in DNS on my local Windows DNS server to a computer.  But I'm not sure what you mean creating a DNS alias to an *instance.*
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40496502
I can easily create an A record in DNS on my local Windows DNS server to a computer.  Hope this makes sense
That is what I thought of.  If you can create A record pointing to theTCP IP server, you can test more easily how the server is resolved.
0
 
LVL 3

Expert Comment

by:Richard Obenchain
ID: 40496515
Okay, first troubleshooting question: from the VM itself, can you access SQL?  Try creating an ODBC connection on the VM to access a database on the VM's SQL server.  If that doesn't work, it's an SQL issue.  If that works, it's some kind of network or port issue between the VM and the other machine(s).
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40496545
Make sure the VM properly maps the physical NIC to the virtual SQL Server NIC.
0
 
LVL 9

Author Comment

by:dmessman
ID: 40500631
Hi all,

I'm savvy with VMs and networking, so I'm pretty certain it's not a non-SQL issue.  The VM is accessible from the LAN.  I can ping the VM's internal IP address.  I can also telnet to the server on port 49205.

Richard - you suggested "Try creating an ODBC connection on the VM to access a database on the VM's SQL server."

Can you give me a little more detail on what that means?  I'm not sure what you mean by an ODBC connection.  Is this SQL Management Studio?  Or something else?
0
 
LVL 3

Expert Comment

by:Richard Obenchain
ID: 40500660
Start menu, search "ODBC".  Data sources control panel should come up.

Click "add" (in either User or System, doesn't matter), and select "SQL Server".

Name it something, description can be blank, and put the server name (or localhost), INCLUDING the instance (so, like, localhost/instancename) in the Server section.  Click "next".

Assuming the installed user was given SA rights on the server (which is pretty standard), just hit "next".  If not, select "With SQL Server authentication..." and set the user ID and password appropriately.  Hit "next".

If you don't get an error, your server is up and listening correctly (and you can just cancel out of the test if you like).  If you do, then there's a problem (and the error message should help isolate that).
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40500680
<<so I'm pretty certain it's not a non-SQL issue.  >>
If you can telnet the VM guest OS that effectively leads to a SQL IP resolution issue.  In this case you will have to do some testing:
> Disable all non TCPIP protocols.  Turn off the SQL Browser service leaving only SQL main service running.  This is an OFFLINE operation.
> Change the default port from 1433 to some value (ex:4533)
> Try connecting to the internal with the port.  From SSMS, enter 264.63.365.36, 4533

That is a good way you can guarantee that SQL is not trying to resolve the connection otherwise than by TCPIP.

If you pass this test, you can be assured that the reason you can not connect is because SQL is not resolving TCIP.  If you don't pass the test, please double check your IP configuration.  This probably has to do with how the instance was installed (DEFAULT vs NAMED).  Please try these and let us know.

Hope this helps.
0
 
LVL 9

Author Comment

by:dmessman
ID: 40537120
Sorry for the delay in responding.  A user reported the ability to connect, but it turns out this is isolated to that one user.  I can't explain it, but I'm treating things as things are not at all standard and I need to make things work the right way.  Richard's last comment asked me to run an ODBC test.  I get these answers:

ODBC test
ODBC results
I also find that I cannot connect from the machine itself to port 1433.  So what does this all mean?  I look forward to your thoughts.

telnet results
0
 
LVL 9

Author Comment

by:dmessman
ID: 40538330
update . . . I saw this:

SQL 1433 port change
I found this here:
http://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/

After making that change, I can now telnet to port 1433 on the server.  I noted earlier that I could telnet to port 49205 on the SQL server, which was the dynamic port.  As of now, I can telnet to 1433 but not to 49205.
0
 
LVL 9

Accepted Solution

by:
dmessman earned 0 total points
ID: 40542676
To wrap this up, I think the problem was never on my end.  The problem was not explained to me correctly.

I was told the *user* could not access SQL server.  But then I was later told that the SQL *admin* could reach the SQL server, but the user could not.

What I should have been told was that the the user was getting an error when trying to run a some kind of function after connecting to the server with Windows authentication and the admin was not getting the error.  

In the end, I remoted into the user's machine.  I logged in as the user and saw the error.  Then I logged in as the SQL admin on the user's machine, and I was able to run the requested function.  As such, it's not a laptop problem, it's  a user permissions issue - and the admin made me run around and solve something that was never wrong.
0
 
LVL 9

Author Closing Comment

by:dmessman
ID: 40550843
nothing was ever really wrong.
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

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 ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

630 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