December 11, 2013 at 4:13 pm
Hello, I'm having an issue accessing SQL Server 2012 Express remotely. I have the following setup:
SQL Server 2012 Express on Windows Server 2012. I'm trying to access SQL Server remotely from Win 7 VM (Parallels Desktop 9) on MacBook Pro. I'm able to login to Windows Server box via remote desktop from Win 7 VM, but unable to connect to SQL Server from Win 7 VM using SQL Server Management Studio within Win 7. Keep getting SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified. Any help on needed configuration changes would be greatly appreciated.
December 11, 2013 at 4:31 pm
1. Do a check whether the sql services are run.
2. If you don't know the Login/Account under which sql server was installed then you can create a new Login:
Go run command promt, and type
- SQLCMD –S <Server_Name\Instance_Name>
- CREATE LOGIN <Login_Name> WITH PASSWORD='<Password>'
GO
- SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'
GO
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
December 11, 2013 at 5:16 pm
Checked the following:
SQL Server (SQLEXPRESS01) Running
SQL Full Text Filter Daemon Launcher (SQLEXPRESS01) Running
SQL Server Reporting Services (SQLEXPRESS01) Running
SQL Server Agent (SQLEXPRESS01) Stopped
SQL Server Browser (SQLEXPRESS01) Stopped
December 11, 2013 at 8:12 pm
I was able to get Sqlbrowser to start. Not able to get Sql Server agent to start. Still unable to connect remotely
December 12, 2013 at 1:26 am
How about the remote access option in sp_configure?
exec sys.sp_configure 'show advanced options',1
reconfigure with override
go
exec sp_configure
Post your error message...
Igor Micev,My blog: www.igormicev.com
December 12, 2013 at 10:53 am
Not 100% sure how to run sp_configure. Is there a GUI to execute the command?
December 12, 2013 at 11:26 am
covershaker45 (12/12/2013)
Not 100% sure how to run sp_configure. Is there a GUI to execute the command?
How do you plan to execute the sp_configure if you don't have a sql login? do you have?
If not, you can create one (run cmd as administrator) and follow the fist post of me.
It looks like you're not a server/db admin? If so, than ask the db admin to create a login for you.
Igor Micev,My blog: www.igormicev.com
December 12, 2013 at 8:53 pm
I was able to execute sp_configure, see messages below.
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
name minimummaximumconfig_valuerun_value
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 10 10
remote proc trans 0 1 0 0
December 12, 2013 at 9:58 pm
I can think of several potential issues:
1. TCP/IP may not be enabled for the SQL instance, this is the default behavior for SQL Express, no need to allow external connections.
2. Browser is not running so it can't find the named instance.
3. The firewall is not allowing the inbound traffic.
CEWII
December 13, 2013 at 3:55 am
I agree with Elliott--one of those three things is almost certainly what's causing your issue. You say you've managed to start the Browser service, so that's one thing fixed (you should probably set that service to Automatic start so it starts when your computer does)--the Agent service not starting is perfectly normal, Agent is one of the features that isn't operational in Express Edition.
To check if remote access is enabled, use SQL Configuration Manager. Check the "Client Protocols" under SQL Native Client 11.0 Configuration and make sure TCP/IP is enabled there. Similarly, enable TCP/IP in the "Protocols for XXX" (where XXX is the instance name of your SQL installation) section under SQL Server Network Configuration. As for the firewall, you need to ensure that remote access is allowed to SQLSERVR.EXE and SQLBROWSER.EXE (note that the latter file is not instance-specific, so it won't be in the same location).
December 13, 2013 at 5:25 pm
To check if remote access is enabled, use SQL Configuration Manager. Check the "Client Protocols" under
Checked:
SQL Native Client 11.0 Configuration (32 bit), Client Protocols, TCP/IP enabled
SQL Server Network Configuration, Protocols for SQLExpress01, TCP/IP enabled IP Address IP1 enabled - no, IP2 enabled - no, ip3 enabled - Yes, ip4 enabled - No, ip5 enabled - , ip6 enabled - , ip7 enabled - No, ip8 enabled - No
SQL Native Client 11.0 Configuration, Client Protocols, TCP/IP enabled
How do I enable firewall access to SQLSERVR.EXE and SQLBROWSER.EXE?
December 13, 2013 at 8:11 pm
From the Control Panel, Network and Sharing Center, at the bottom on the left is windows firewall, the simplest to TEST is to turn the firewall off completely, if you can then connect then the firewall is the issue if you can't then it might still be an issue but you can't be sure yet. If you get connected then turn the firewall back on.
And look over http://technet.microsoft.com/en-us/library/cc646023.aspx
CEWII
December 13, 2013 at 9:28 pm
Disabled Server firewall still not able to access SQL Express. After disabling and then enabling I'm getting new message "Login Fail for user Server\Administrator Error 18456" I know that the login ID is valid. When I use remote desktop I'm able to connect to the SQL server with same ID. Could this be a issue with my Win 7 (VM) setup or do you guys still think that the issues resides on the SQL Server side?
December 14, 2013 at 2:26 pm
So when you disabled NOTHING changed, BUT you got a difference after you re-enabled the firewall?
CEWII
December 15, 2013 at 9:01 pm
Yes, got difference message. After reviewing all replies, I noticed that I didn't disable the domain firewall. I only disable the public and private firewalls. After disabling the domain firewall I was able to access the SQL Server. I enabled it and couldn't access it once again. I switched the Inbound Connects to Allow for the Domain, restarted the server and it is now connecting with Domain firewall enabled. I think that I have what I need. Thanks for suggestions on what to check.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply