Solved

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

Posted on 2007-04-03
6
1,162 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
ID: 18843770
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
ID: 18843792
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
ID: 18843797
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Biziteks
ID: 18843964
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
ID: 18846317
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
ID: 19417991
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

685 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