Allowing remote connections to Sql Server from command line

Hi,

Sql Server doesn't allow remote connections by default. I'm looking for a way to enable remote connections without using the UI. Ideally, I would like a command line tool or a script, though writing a C# tool would be better than nothing.

The way to do this though the UI is to go to the Surface Area Configuration > Services and Connections > MSSQLSERVER > Database Engine > Remote Connections and select "Local and remote connections" and "Using TCP/IP only".

Does anyone know how to do this programmatically?

Thanks,

Ann

Yes, check out the SAC utility:

http://msdn2.microsoft.com/en-us/library/ms162800(SQL.90).aspx

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server

http://blogs.msdn.com/sqlrem/

Allowing remote connections during setup?

Is there a way to automatically setup SQL Server Express to allow remote connections during the installation process?

We are deploying SQL Server Express with our application and I really can't ask SMB customers to go through a series of rather complicated steps after our "turn key" setup installs everything in order for any of the other computers in their office to connect.

What's the reasoning behind that, anyway? Why create a database server setup which by default doesn't allow anyone except the server to access it? I guess that makes sense for ASP.NET but the web fad isn't the only platform developers use these days.

Any assistance would be greatly appreciated.

Cheers,

Evan

Evan, have you played around with the DISABLENETWORKPROTOCOLS switch? The default for Express is TCP=Off (1). This is the snippet from BOL.

;--
; The DISABLENETWORKPROTOCOLS switch is used to disable network protocol for SQL Server instance.
; Set DISABLENETWORKPROTOCOLS = 0; for Shared Memory= On, Named Pipe= On, TCP= On
; Set DISABLENETWORKPROTOCOLS = 1; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= Off
; Set DISABLENETWORKPROTOCOLS = 2; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= On

; Note: DISABLENETWORKPROTOCOLS if not specified has the following defaults.
; Default value for SQL Server Express/Evaluation/Developer: DISABLENETWORKPROTOCOLS =1
; Default value for Enterprise/Standard /Workgroup: DISABLENETWORKPROTOCOLS =2

Thanks,
Samuel Lester (MSFT)

Allowing Remote Connections

When I attempt to connect to our new SQLServer 2005 from my workstation running the latest XP Pro and SQL Server Mgmt Studio 2005, I get the following error. I checked the setup on the server, and TCP/IP connections are allowed. I connect to all our older SQL Server 2000 servers/databases just fine.

TITLE: Connect to Server

Cannot connect to <server>\<instance> (deleted).

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

BUTTONS:

OK

Terry,

I'm going to move this thread to the "Data Access" forum where I think you'll get a quicker response.

-Jeffrey

|||

Terry,

The error message shows that the sql browser is not able to be contacted by the client. So you need to (1) enble the sql browser service and start it. (2) make exception on your firewall setting to allow 1434 udp port.

Otherwise, you can specify port number of your sqlserver in your connection string. For example. tcp:<servername>,<serverport>

SQL Browswer is used to resolve connection paramenter for named instance. It is a seperate service from SQL Server.

|||

Terry,

I didn't do what the one reply did, but it gave me a clue on where to look.

SQL 2005 has a new SQL Server Configuration Manager under Configuration Tools. Click on that, after it opens go to SQL Server 2005 Services and verity that SQL Server Browser is running.

Now this is the change I had to make. Open SQL Server 2005 Network Configuation, and click on Protocols for <server>. You should just need to enable TCP/IP then restart SQL Server 2005 Service, if you like to you can enable the others if you use them here.

Hope this helps.

Randy

Allowing Remote Connections

When I attempt to connect to our new SQLServer 2005 from my workstation running the latest XP Pro and SQL Server Mgmt Studio 2005, I get the following error. I checked the setup on the server, and TCP/IP connections are allowed. I connect to all our older SQL Server 2000 servers/databases just fine.

TITLE: Connect to Server

Cannot connect to <server>\<instance> (deleted).

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

BUTTONS:

OK

Terry,

I'm going to move this thread to the "Data Access" forum where I think you'll get a quicker response.

-Jeffrey

|||

Terry,

The error message shows that the sql browser is not able to be contacted by the client. So you need to (1) enble the sql browser service and start it. (2) make exception on your firewall setting to allow 1434 udp port.

Otherwise, you can specify port number of your sqlserver in your connection string. For example. tcp:<servername>,<serverport>

SQL Browswer is used to resolve connection paramenter for named instance. It is a seperate service from SQL Server.

|||

Terry,

I didn't do what the one reply did, but it gave me a clue on where to look.

SQL 2005 has a new SQL Server Configuration Manager under Configuration Tools. Click on that, after it opens go to SQL Server 2005 Services and verity that SQL Server Browser is running.

Now this is the change I had to make. Open SQL Server 2005 Network Configuation, and click on Protocols for <server>. You should just need to enable TCP/IP then restart SQL Server 2005 Service, if you like to you can enable the others if you use them here.

Hope this helps.

Randy

Allowing Local and Remote connections breaks SQL queries

hello
I have a remote server in a datacentre that hosts our website
(www.sheffcare.co.uk) and we have an online backup service that tries
to backup out \inetpub\www and SQL files.
The thing is, that everytime the the backup runs, the website SQL
queries break, with the following errors:
Microsoft JET Database Engine error '800004005'
Operation must use an updateable query
/web_managers/calendarAmend.asp, line 88
Also logged in the System Even Log are:
1. Login failed for user 'administrator'
2. SSPI handshake failed with error code 0x8009030c while establishing
a connection with integrated security; the
connection has been closed
3. Login failed for user ''. The user is not associated with a trusted
SQL Server connection.
It seems the only way to get the website working again is to go into
SQL Server Suface Area Configuration application and change Remote
Connections from Local and Remote to Local only and restart the
SQLEXPRESS service.
But we need to allow Local and Remote Connections for the online
backup to work.
Could this be a problem with the code?
Thanks for any pointers.What backup program are you using?
T-SQL 'BACKUP DATABASE' won't do this. Idera LiteSpeed won't either.
Because you said "online backup service that tries to backup out
\inetpub\www and SQL files" I get the feeling you're doing some filesystem
based backup and not a database backup. I've never done that.
If I'm correct, then a simple and fast solution is to not backup the .mdf,
.ndf & .ldf files directly and create a maintenance plan - except that
maintenance plans are 2000 and I think you have 2005.
"cw1972" <cw1972@.gmail.com> wrote in message
news:1192439902.757094.12590@.e9g2000prf.googlegroups.com...
> hello
> I have a remote server in a datacentre that hosts our website
> (www.sheffcare.co.uk) and we have an online backup service that tries
> to backup out \inetpub\www and SQL files.
> The thing is, that everytime the the backup runs, the website SQL
> queries break, with the following errors:
> Microsoft JET Database Engine error '800004005'
> Operation must use an updateable query
> /web_managers/calendarAmend.asp, line 88
>
> Also logged in the System Even Log are:
> 1. Login failed for user 'administrator'
> 2. SSPI handshake failed with error code 0x8009030c while establishing
> a connection with integrated security; the
> connection has been closed
> 3. Login failed for user ''. The user is not associated with a trusted
> SQL Server connection.
> It seems the only way to get the website working again is to go into
> SQL Server Suface Area Configuration application and change Remote
> Connections from Local and Remote to Local only and restart the
> SQLEXPRESS service.
> But we need to allow Local and Remote Connections for the online
> backup to work.
> Could this be a problem with the code?
> Thanks for any pointers.
>|||On 15 Oct, 14:31, "Jay" <s...@.nospam.org> wrote:
> What backup program are you using?
it's an online backup prodcedure provided by these people -
http://www.databarracks.com/OnlineBackup/Technology/Software/SupportedApplications/
- they say SQL server is fully supported
> T-SQL 'BACKUP DATABASE' won't do this. Idera LiteSpeed won't either.
> Because you said "online backup service that tries to backup out
> \inetpub\www and SQL files" I get the feeling you're doing some filesystem
> based backup and not a database backup. I've never done that.
> If I'm correct, then a simple and fast solution is to not backup the .mdf,
> .ndf & .ldf files directly and create a maintenance plan - except that
> maintenance plans are 2000 and I think you have 2005.
>
I'm unsure what a maintenance plan is, I'll have to look into it - we
are using SQL Express 2005|||I am not familar with that backup program, however, I would suggest asking
their support.
As to the maintenance plans, SQL Server Express does not support maintenance
plans, or SQL agent. So, to automate anything, you have to use the Windows
scheduler and SQLCMD (see BOL).
What is it you are trying to backup? Just the database, or the database and
the website? I still have the feeling that you're trying to use one
application to back everything up. While I suppose that's possible, I don't
think it's a good idea.
Just to get yourself covered, in a query window, for each database you care
about (including master) run:
BACKUP DATABASE master TO DISK='X:\Backups\master.BAK' WITH INIT
(replacing the dbname and the path, making sure everything exists)
"cw1972" <cw1972@.gmail.com> wrote in message
news:1192458718.529399.35120@.e9g2000prf.googlegroups.com...
> On 15 Oct, 14:31, "Jay" <s...@.nospam.org> wrote:
>> What backup program are you using?
> it's an online backup prodcedure provided by these people -
> http://www.databarracks.com/OnlineBackup/Technology/Software/SupportedApplications/
> - they say SQL server is fully supported
>> T-SQL 'BACKUP DATABASE' won't do this. Idera LiteSpeed won't either.
>> Because you said "online backup service that tries to backup out
>> \inetpub\www and SQL files" I get the feeling you're doing some
>> filesystem
>> based backup and not a database backup. I've never done that.
>> If I'm correct, then a simple and fast solution is to not backup the
>> .mdf,
>> .ndf & .ldf files directly and create a maintenance plan - except that
>> maintenance plans are 2000 and I think you have 2005.
> I'm unsure what a maintenance plan is, I'll have to look into it - we
> are using SQL Express 2005
>

Allow remote connection (using TCP/IP) from SQL Ce

Hi all,

Need to know if its possible to allow remote connections and access a Sql Ce database using TCP/IP.

Thanks for your help

Hach

No, this is not possible out of the box. What are you trying to acheive? Sounds like SQL Express can help you - it allows remote connections over TCP/IP.|||Your app is the host, so there is no seperate process like bigger sql brothers that listen on an endpoint. You can provide your own listeners using various techs such as wcf, sockets, remoting, etc.