Remote SQL Server

I've got a customer who has an application written in Access. They are
hitting some boundaries of Access, and are ready to move to SQL. They are
going to keep the front end in Access b/c of the massive amounts of code
they have written. We are going to set them up a SQL server in a datacenter,
and use linked tables in their access database. They have offices all over
the eastern half of the US, so they will need to connect to the SQL server
from anywhere, pretty much.
Basically my question is how can I keep this secure? I'm going to setup
encryption. Is it a bad idea to leave SQL server open on the public? How
can I lock it down and still keep functionality?I get about 10-20,000 hits a night from what looks like dictionary attacks.
Make sure all users & sa user have a a very long non standard password.
Can you lock the firewall down to a list of known host ranges on port 1433
?.
Run MS Base line security application on sql server box !.
Remove all non essensial db's on sql installation.
Regards
Don Grover
"Chris Miller" <cmiller@.compuville.net> wrote in message
news:1010agvq9v9k154@.corp.supernews.com...

quote:

---

> I've got a customer who has an application written in Access. They are
> hitting some boundaries of Access, and are ready to move to SQL. They are
> going to keep the front end in Access b/c of the massive amounts of code
> they have written. We are going to set them up a SQL server in a

---

datacenter,

quote:

---

> and use linked tables in their access database. They have offices all over
> the eastern half of the US, so they will need to connect to the SQL server
> from anywhere, pretty much.
>
> Basically my question is how can I keep this secure? I'm going to setup
> encryption. Is it a bad idea to leave SQL server open on the public? How
> can I lock it down and still keep functionality?
>

---

|||I'm going to try to lock it down to only a list of known host ranges, but
I'm not sure that will be possiable.
Where do I set what hosts each user can login from? Also, how do I set sa to
only be allowed to connect from localhost?
-Chris
"Don Grover" <spamfree@.assoft.com.au> wrote in message
news:%23JhsCNS4DHA.2404@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I get about 10-20,000 hits a night from what looks like dictionary

---

attacks.

quote:

---

> Make sure all users & sa user have a a very long non standard password.
> Can you lock the firewall down to a list of known host ranges on port 1433
> ?.
> Run MS Base line security application on sql server box !.
> Remove all non essensial db's on sql installation.
> Regards
> Don Grover
> "Chris Miller" <cmiller@.compuville.net> wrote in message
> news:1010agvq9v9k154@.corp.supernews.com...
are[QUOTE]
> datacenter,
over[QUOTE]
server[QUOTE]
How[QUOTE]
>

---

|||Chris, I hope you don't mind if I piggyback my question onto yours;
We also have the need to make a sql database accessibly via internet. We
think our customers are going to demand encryption over the wire.
sql2k allows for ssl encryption at the protocol level, but my understanding
is that this would require our customers to enable ssl encryption on any sql
server they access from a given client. Am I wrong?
I recall seeing something about using the keyword "encrypt", either in the
connection string or in sql commands, but can find this nowhere in any dox.
Can anyone help me here?
So, what is the best way to do this?
Jeremy
"Chris Miller" <cmiller@.compuville.net> wrote in message
news:1010agvq9v9k154@.corp.supernews.com...

quote:

---

> I've got a customer who has an application written in Access. They are
> hitting some boundaries of Access, and are ready to move to SQL. They are
> going to keep the front end in Access b/c of the massive amounts of code
> they have written. We are going to set them up a SQL server in a

---

datacenter,

quote:

---

> and use linked tables in their access database. They have offices all over
> the eastern half of the US, so they will need to connect to the SQL server
> from anywhere, pretty much.
>
> Basically my question is how can I keep this secure? I'm going to setup
> encryption. Is it a bad idea to leave SQL server open on the public? How
> can I lock it down and still keep functionality?
>

---

|||I too will be using SLL Encryption via my ODBC connections.
-Chris
"Jeremy" <grand@.hevanet.com> wrote in message
news:%23NKtQwW4DHA.2136@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Chris, I hope you don't mind if I piggyback my question onto yours;
> We also have the need to make a sql database accessibly via internet. We
> think our customers are going to demand encryption over the wire.
> sql2k allows for ssl encryption at the protocol level, but my

---

understanding

quote:

---

> is that this would require our customers to enable ssl encryption on any

---

sql

quote:

---

> server they access from a given client. Am I wrong?
> I recall seeing something about using the keyword "encrypt", either in the
> connection string or in sql commands, but can find this nowhere in any

---

dox.

quote:

---

> Can anyone help me here?
> So, what is the best way to do this?
> Jeremy
>
> "Chris Miller" <cmiller@.compuville.net> wrote in message
> news:1010agvq9v9k154@.corp.supernews.com...
are[QUOTE]
> datacenter,
over[QUOTE]
server[QUOTE]
How[QUOTE]
>

---

|||"Chris Miller" <cmiller@.compuville.net> wrote in
news:1010c2rc2c6kc99@.corp.supernews.com:

quote:

---

> I'm going to try to lock it down to only a list of known host ranges,
> but I'm not sure that will be possiable.
> Where do I set what hosts each user can login from? Also, how do I set
> sa to only be allowed to connect from localhost?

---

Did you find an answer to how you limit sa to localhost? I very like to
know this answer.
Masa|||By default there is no mechanism to do this. You could limit the machines
allowed to connect to the server via firewall or using Microsoft ISA server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin, are you saying there is no mechanism whatsoever, other than the
global ssl technique, to encrypt data over the wire? Not even a 3rd party
solution?
And yes, we can limit connections to known machines, but the market is
demanding encryption. What argument do we use to convince folks that
unencrypted transfers are ok?
Thanks, Jeremy

quote:

---

> By default there is no mechanism to do this. You could limit the machines
> allowed to connect to the server via firewall or using Microsoft ISA

---

server.

quote:

---

> Thanks,
> Kevin McDonnell
> Microsoft Corporation

---

|||My response was to a question regarding limiting the network interfaces.
I'm not sure what you meant by this reply:
"are you saying there is no mechanism whatsoever, other than the
global ssl technique, to encrypt data over the wire? Not even a 3rd party
solution?"
We can use SSL encryption with SQL 2000, which relies on PKI.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

Remote database Connection Error!

Hi,
My Application which is written in VB.Net uses database connection to my
Local Instance of SQL Server Express version.
My connection string is " Data
Source=SM5\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MgmtSystem.mdf;Initial Catalog=MgmtSystem;Persist
Security Info=True;User ID=sa;Password=belmont". When I run my application
from the same machine where SQL Server instance is installed, it works fine.
But now I am intending to use it as Client-Server type as database on Server
and application running from another machine. To test this I have deployed
my application on another machine. When I run it I get following error, "An
error has occured 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 setting SQL Server does not allow remote
connections.(provider: SQL Network Interface, error: 26 - Error Locating
Server/Instance specified)(Microsoft SQL Server Error:-1).
To Fix this I did try enabling all protocols(Shared Memory, TCP/IP, Named
Pipe). Then I even started SQL Browser service to enable the instance to be
browsed by client. Then Checked if Hide Instance property. Still no Luck.
I am really going crazy. This is really important for me since we are
migrating from Access to SQL Server Express. Since in Access my application
can have client server
Architecture. Same thing I need to have in SQL Express.
Any help is really appreciated.
Thanks,
Sujatause sql2k5 surface area configuration tool to enable 'remote connections'.
--
-oj
"Sujata P" <SujataP@.discussions.microsoft.com> wrote in message
news:94D32EEB-9098-4A51-B55D-95BD84B5AA96@.microsoft.com...
> Hi,
> My Application which is written in VB.Net uses database connection to my
> Local Instance of SQL Server Express version.
> My connection string is " Data
> Source=SM5\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\MgmtSystem.mdf;Initial
> Catalog=MgmtSystem;Persist
> Security Info=True;User ID=sa;Password=belmont". When I run my application
> from the same machine where SQL Server instance is installed, it works
> fine.
> But now I am intending to use it as Client-Server type as database on
> Server
> and application running from another machine. To test this I have
> deployed
> my application on another machine. When I run it I get following error,
> "An
> error has occured 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 setting SQL Server does not allow remote
> connections.(provider: SQL Network Interface, error: 26 - Error Locating
> Server/Instance specified)(Microsoft SQL Server Error:-1).
> To Fix this I did try enabling all protocols(Shared Memory, TCP/IP, Named
> Pipe). Then I even started SQL Browser service to enable the instance to
> be
> browsed by client. Then Checked if Hide Instance property. Still no Luck.
> I am really going crazy. This is really important for me since we are
> migrating from Access to SQL Server Express. Since in Access my
> application
> can have client server
> Architecture. Same thing I need to have in SQL Express.
> Any help is really appreciated.
> Thanks,
> Sujata|||Thanks oj...but I found answer...Actually I did everything last time except
creating exceptions in Windows Firewall for instance of my SQL Server to
unblock the connection.
For anyone who is facing similar problem, Follow
http://support.microsoft.com/?id=914277..it is very useful for creating
remote connection.
"oj" wrote:
> use sql2k5 surface area configuration tool to enable 'remote connections'.
> --
> -oj
>
> "Sujata P" <SujataP@.discussions.microsoft.com> wrote in message
> news:94D32EEB-9098-4A51-B55D-95BD84B5AA96@.microsoft.com...
> > Hi,
> > My Application which is written in VB.Net uses database connection to my
> > Local Instance of SQL Server Express version.
> > My connection string is " Data
> > Source=SM5\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL
> > Server\MSSQL.1\MSSQL\Data\MgmtSystem.mdf;Initial
> > Catalog=MgmtSystem;Persist
> > Security Info=True;User ID=sa;Password=belmont". When I run my application
> > from the same machine where SQL Server instance is installed, it works
> > fine.
> > But now I am intending to use it as Client-Server type as database on
> > Server
> > and application running from another machine. To test this I have
> > deployed
> > my application on another machine. When I run it I get following error,
> > "An
> > error has occured 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 setting SQL Server does not allow remote
> > connections.(provider: SQL Network Interface, error: 26 - Error Locating
> > Server/Instance specified)(Microsoft SQL Server Error:-1).
> >
> > To Fix this I did try enabling all protocols(Shared Memory, TCP/IP, Named
> > Pipe). Then I even started SQL Browser service to enable the instance to
> > be
> > browsed by client. Then Checked if Hide Instance property. Still no Luck.
> > I am really going crazy. This is really important for me since we are
> > migrating from Access to SQL Server Express. Since in Access my
> > application
> > can have client server
> > Architecture. Same thing I need to have in SQL Express.
> >
> > Any help is really appreciated.
> >
> > Thanks,
> > Sujata
>
>

Remote Connection Fails with Web Service

Hello-
I am having a problem with a Web Service written in Visual Studio 2005.
I have a typed dataset that I created using the Visual Studio 2005
Data Source tool. This retrieves its data from a SQL Server 2000
database. My client calls a web service which calls a business
component to retrieve the typed dataset (i.e. Client --> Web Service
--> Business Component).
Whenever I put the web service between my client and my business
component I get the following error:
Server was unable to process request. --> 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: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)
If I do not put my web service in between my client and business
component, I do not get this error (i.e. Client --> Business
Component).
I have made sure that my SQL 2005 has Remote Connections enabled with
both Named Pipes and TCP/IP.
I don't understand why putting a web service between my client and
business component is making this error appear or how to remedy the
issue. Any help would be much appreciated!
-DrewI found the problem. In my web.config I had to add the following
configuration: <identity impersonate="true" />.
-Drew