Solved

SQL Server Does Not Allow Remote Connections When Using sqlcmd on Local Machine

Posted on 2007-04-03
6
1,111 Views
Last Modified: 2012-06-27
I am trying to backup databases in SQL 2005 Express on SBS 2K3 SP1, which doesn't have the SQL Agent. I am attempting to access the database directly from the server it is installed on. I want to use a batch file to access the database and run the backup T-SQL command, but it tells me the SQL server does not allow remote connections. I have verified that remote connections was enabled in the Surface Area Configuration, and also double-checked that the SQL Server Browser process is running. I am pretty sure that the server has been rebooted since those settings were enabled. I have tried using sqlcmd and osql, but neither is successful. Actually, osql gives me doesn't exist or access denied errors. Any help would be greatly appreciated.

Biziteks
0
Comment
Question by:Biziteks
6 Comments
 
LVL 4

Expert Comment

by:davidsperling
Comment Utility
Guess you've read a similar instruction like this:
http://www.datamasker.com/SSE2005_NetworkCfg.htm

For me it didn't work until I checked "Using both TCP/IP and named pipes" in surfice area configurator. -And rebooted, I guess.

(My problem was that I couldn't connect from visual studio. Using sqlserver2k5 express.)
 
0
 

Author Comment

by:Biziteks
Comment Utility
Yeah, I did see that article and a couple other ones, including a KB from Microsoft, I think. The option for both was already checked when I went in Surface Area Configuration. If all else fails I will reboot the server tonight for kicks and giggles, and hopefully fix this. Any additional suggestions are always welcome.

Biziteks
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
The remote connections message is a red herring. You are simply not passing the correct server name or do not have the right permissions.

Try using '.' to identify the server when connecting or append /SqlExpress to the real server name.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Biziteks
Comment Utility
When I use Management Studio, I see <servername>\<databasename> in the Server name field. I can login this way using either sa or Windows Authentication. I have given the Windows domain user (also a domain admin acct) access to all the Server Roles and went in User Mapping and selected the user to have all roles for the db. I still cannot get it to run.

An error that I missed earlier pops up before the remote connections error and reads: HResult 0x15, Level 16, State 1  Encryption not supported on the client.

Biziteks
0
 
LVL 5

Expert Comment

by:nakul_vachhrajani
Comment Utility
Try installing latest version of the SQL 2005 feature pack - that will give you support for osql commands (however, I doubt they will be of much help on SQL 2005 Express).
0
 
LVL 3

Accepted Solution

by:
abhijit_k earned 250 total points
Comment Utility
Go to Start-Control Panel-Administrative TOOLs-Component Services
Right CLick and go to Security Configuration and Check the Boxes allow inbound Connections
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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