Solved

Connecting SQL 2014 Management Studio to SQL Server 2014 Express

Posted on 2014-12-11
16
347 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
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
Comment Utility
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
Comment Utility
can you tell us more about how and where (as opposed to your LAN) the consultant needs to connect.
0
 
LVL 9

Author Comment

by:dmessman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 23

Expert Comment

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

Author Comment

by:dmessman
Comment Utility
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
Comment Utility
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
Comment Utility
<<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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
nothing was ever really wrong.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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

11 Experts available now in Live!

Get 1:1 Help Now