High security Needs, Cheep Clients, Need Help

I have an existing server hosting several sites with associated back end
databases.
I have a new client who needs secure hosting solutions, including a secure
database, without the expense of a new server. I was thinking of the
following architecture. I know some ( most ) of this is not SQL related,
but bear with me please.
I am hosting web services on the server to provide the interface with the
external world. End-Use clients will have applications with certificates to
access these web services. I have two instances of SQL Server 2005 running,
MYSERVER\PUBLIC & MYSERVER\PRIVATE.
MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
info relevant to the new service which need not be secure.
MYSERVER\PRIVATE will contain all the new, to be secured, data.
MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
available only as 'localhost' from within the server, i.e. not be public to
the outside world. My web services should be able to access this data, and
the web services will only be accessible to these signed applications. Of
course, any relevant info would be encrypted.
Is this sufficient ?
Any input would be appreciated
Thanks
PhilYou have to make sure that you are using different service accounts for each
instance otherwise someone gaining elevated rights within one instance will
be able to get to the other instance. Additionally, you should not allow
any SQL logins to either instance. The web server should be configured to
connect to the instances using Windows credentials which forces anyone who
can not authenticate in Windows to not have access to either database
instance. The IIS server needs to be locked down to ensure that you don't
have any security holes. The service accounts for each instance should be
regular Windows accounts and NOT a member of local administrators or even
worse domain/enterprise administrators.
After accomplishing that, you pretty much have the front door closed. Now
you have to worry about all of the back end stuff. How are you going to
handle backups of the databases such that the data in your secure instance
remains secure even through the backup files? How are you going to control
administrator and developer access within your company to the secured
instance? How are you going to audit the operations within the secure
instance such that your customer on this secure instance can't create a
situation where they actually damaged data or caused the security to be
compromised, but are pointing the finger at you?
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Phil" <prounds@.cassandragroup.com> wrote in message
news:eFeo0WchGHA.4080@.TK2MSFTNGP03.phx.gbl...
>I have an existing server hosting several sites with associated back end
>databases.
> I have a new client who needs secure hosting solutions, including a secure
> database, without the expense of a new server. I was thinking of the
> following architecture. I know some ( most ) of this is not SQL related,
> but bear with me please.
> I am hosting web services on the server to provide the interface with the
> external world. End-Use clients will have applications with certificates
> to access these web services. I have two instances of SQL Server 2005
> running, MYSERVER\PUBLIC & MYSERVER\PRIVATE.
> MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
> info relevant to the new service which need not be secure.
> MYSERVER\PRIVATE will contain all the new, to be secured, data.
> MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
> available only as 'localhost' from within the server, i.e. not be public
> to the outside world. My web services should be able to access this data,
> and the web services will only be accessible to these signed applications.
> Of course, any relevant info would be encrypted.
> Is this sufficient ?
> Any input would be appreciated
> Thanks
> Phil
>|||"Phil" <prounds@.cassandragroup.com> wrote in message
news:eFeo0WchGHA.4080@.TK2MSFTNGP03.phx.gbl...
>I have an existing server hosting several sites with associated back end
>databases.
> I have a new client who needs secure hosting solutions, including a secure
> database, without the expense of a new server. I was thinking of the
> following architecture. I know some ( most ) of this is not SQL related,
> but bear with me please.
> I am hosting web services on the server to provide the interface with the
> external world. End-Use clients will have applications with certificates
> to access these web services. I have two instances of SQL Server 2005
> running, MYSERVER\PUBLIC & MYSERVER\PRIVATE.
> MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
> info relevant to the new service which need not be secure.
> MYSERVER\PRIVATE will contain all the new, to be secured, data.
> MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
> available only as 'localhost' from within the server, i.e. not be public
> to the outside world. My web services should be able to access this data,
> and the web services will only be accessible to these signed applications.
> Of course, any relevant info would be encrypted.
> Is this sufficient ?
>
Can't tell if it's sufficient, but it should work.
Also don't even need to enable TCP/IP for the PRIVATE instance. Programs
running on the same box can use the shared memory provider, which only works
locally. Also make sure to put the private web apps in their own
application pool (or web site), and use a seperate set of Windows accounts
to run the secure web apps, connect to SQL and as the SQL Service account.
Segegate the data and use NTFS permissions to restrict read/write to the
database files, logs and backups.
David

High Availability Solutions

Can someone list some HA solutions out there ?
Also with regards to HA wrt SAN
Also curious to know how such HA solutions can help when doing planned
downtime such as installing service packs or upgrading code such as altering
a table,etc..
Might want to have a look here:
http://www.microsoft.com/sql/technol...y/default.mspx
Andrew J. Kelly SQL MVP
"Just Me" <justme@.hotmail.com> wrote in message
news:uhgQJ1j7FHA.736@.TK2MSFTNGP09.phx.gbl...
> Can someone list some HA solutions out there ?
> Also with regards to HA wrt SAN
> Also curious to know how such HA solutions can help when doing planned
> downtime such as installing service packs or upgrading code such as
> altering a table,etc..
>

High Availability solutions

Hi

We are using in our company an ERP system called iScala.

This software is using SQL server as DBMS.

We would like to use the SQL mirroring feature from SQL 2005.

The software is using the SQL server instance name to connect to databases and when the Main SQL server fails the application can't connect to the Mirror instance.

Unfortunatelly we can't change the connection string (the software is provided by another company).

Do you have a solution?

Thanks

I believe you have to contact the vendor to provide such changes, as this is application issue to talk to the mirror server in the event of failover. If the application is not db mirror aware then you have to find a route from connectivity to talk to other server in such cases.|||

Thanks

You are right.

I'm checking now some solutions for High Availability from Symantec (VERITAS Storage Foundation HA 4.3 for Windows) and Double-Take.

Do you have any information about them?

|||

You could solve this issue using a manual change to DNS.

i.e. MYLIVESQL -> 10.0.0.1 - connection string uses MYLIVESQL as the server name, in the event of a failure you update the DNS record to MYLIVESQL -> 10.0.0.2 (which is the IP address of the mirror server).

Andrew

|||

If the (former) principal instance is still up and running but the database failover occured, then SNAC will automatically learn the location of the mirror and redirect the connection to the mirror, as described here: http://msdn2.microsoft.com/en-us/library/ms366348.aspx. But this works only if the connection string specifies the database and if the principal instance is still running.

But if the vendor is not providing support for mirroring, is highly unlikely that the application will actually work with mirrored database, it may have dependencies outside the database that are not gonna failover (e.g. logins resulting in orphaned users, jobs in msdb etc)

HTH,
~ Remus

|||

Remus,

MOSS 2007 and WSS V3 fall into the category of not supporting mirroring in the connection strings, hence the need to use the cname approach. Microsoft recommend using stsadm to manually change the settings which I think is less elegant.

Andrew

High Availability Solutions

Can someone list some HA solutions out there ?
Also with regards to HA wrt SAN
Also curious to know how such HA solutions can help when doing planned
downtime such as installing service packs or upgrading code such as altering
a table,etc..Might want to have a look here:
http://www.microsoft.com/sql/techno...ty/default.mspx
Andrew J. Kelly SQL MVP
"Just Me" <justme@.hotmail.com> wrote in message
news:uhgQJ1j7FHA.736@.TK2MSFTNGP09.phx.gbl...
> Can someone list some HA solutions out there ?
> Also with regards to HA wrt SAN
> Also curious to know how such HA solutions can help when doing planned
> downtime such as installing service packs or upgrading code such as
> altering a table,etc..
>

High Availability Solutions

Can someone list some HA solutions out there ?
Also with regards to HA wrt SAN
Also curious to know how such HA solutions can help when doing planned
downtime such as installing service packs or upgrading code such as altering
a table,etc..Might want to have a look here:
http://www.microsoft.com/sql/technologies/highavailability/default.mspx
Andrew J. Kelly SQL MVP
"Just Me" <justme@.hotmail.com> wrote in message
news:uhgQJ1j7FHA.736@.TK2MSFTNGP09.phx.gbl...
> Can someone list some HA solutions out there ?
> Also with regards to HA wrt SAN
> Also curious to know how such HA solutions can help when doing planned
> downtime such as installing service packs or upgrading code such as
> altering a table,etc..
>

Hierarchies: Any drawbacks doing it this way?

Hi all,
I have a requirement to store hierarchical data in a SQL database. I've
seen several different solutions posted here in the past on how to retrieve
hierarchical data.
In an effort to simplify the storage and retrieval of this data, I was
thinking of initally inserting the data into the table with some type of
"Level" or "Position" column which indicates the position in the hierarchy
of each item. I will need to insert and remove items in the hierarchy
somewhat frequently.
For an insertion into the hierarchy, if I know where the new item's position
should be, I could just insert the new item into the table with that
position number, then increment the item which previously had that
particular position and all the items below it by one. Removing an item
from the hierarchy would follow similar logic.
Are there any drawbacks to storing the hierarchical data this way? Is there
any reason for me to avoid this method of maintaining a "Position" column
for each item?
Any commenets are appreciated,
BenA "hierarchy level" column seems unlikely to be useful for subtree
maintenance in an Adjacency List model (I am assuming adjacency list
because you didn't specify what other model you might be using). In
fact it almost certainly hinders inserts because it is redundant data
that needs to be updated each time - probably on many rows for each row
inserted. Don't store the hierarchy level unless you regularly need to
query that information and have a good reason to denormalize.
For subtree maintenance, Materialized Path is typically the lowest cost
model. Nested Sets is cheap for pruning the tree but unpredictably
costly for grafting on subtrees.
David Portas
SQL Server MVP
--|||Check out this article from Joe Celko:
http://www.dbazine.com/ofinterest/oi-articles/celko24
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Ben Amada" <ben@.REpoMOweVErpick.com> wrote in message
news:OL%23BJww0FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a requirement to store hierarchical data in a SQL database. I've
> seen several different solutions posted here in the past on how to
> retrieve hierarchical data.
> In an effort to simplify the storage and retrieval of this data, I was
> thinking of initally inserting the data into the table with some type of
> "Level" or "Position" column which indicates the position in the hierarchy
> of each item. I will need to insert and remove items in the hierarchy
> somewhat frequently.
> For an insertion into the hierarchy, if I know where the new item's
> position should be, I could just insert the new item into the table with
> that position number, then increment the item which previously had that
> particular position and all the items below it by one. Removing an item
> from the hierarchy would follow similar logic.
> Are there any drawbacks to storing the hierarchical data this way? Is
> there any reason for me to avoid this method of maintaining a "Position"
> column for each item?
> Any commenets are appreciated,
> Ben
>|||Hi David,
This is my first time dealing with hierarchies, so I'm not sure if the model
I'm working with is an Adjacency List model or not.
Below, I've included some DDL and insert statements on the type of data I'm
going to be storing -- the data below is for a single hierarchy tree I'll be
storing -- I'll actually be storing dozens of these trees, so I'll obviously
need another column in the table to identify the Hierarchy ID. There aren't
that many rows per hierarchy though, which is why I was thinking of storing
a "Position" or "Level" column.
When I retrieve the data from the table, the desired order of the data is
the same order of the INSERT statements below -- so "catOuter", "catOne",
"spnOne", etc.
I see myself having two or three options when retrieving the data:
(1) Create and maintain a "Position" column.
(2) Create a stored procedure or UDF which calls itself recursively to
retrieve the hierarchy in the desired order.
I suppose a 3rd option would be some set-based solution which is what I
usually try to do, but for hierarchies, I'm not sure how to implement a
set-based solution.
After seeing the sample data below, I'm interested in which option (1, 2 or
3) you think might work best?
Thanks again,
Ben
--
create table PageElements
( ElementID varchar(25) primary key,
ParentID varchar(25) )
insert into PageElements
(ElementID, ParentID)
select 'catOuter', NULL union all
select 'catOne', 'catOuter' union all
select 'spnOne', 'catOne' union all
select 'imgOne', 'catOne' union all
select 'catTwo', 'catOuter' union all
select 'spnTwo', 'catTwo' union all
select 'catThree', 'catOuter' union all
select 'spnThree', 'catThree' union all
select 'spnFour', 'catThree' union all
select 'spnFive', 'catThree' union all
select 'imgTwo', 'catThree' union all
select 'imgThree', 'catThree' union all
select 'spnSix', 'catThree' union all
select 'spnSeven', 'spnSix' union all
select 'spnEight', 'spnSix' union all
select 'spnNine', 'spnSix' union all
select 'spnTen', 'spnSix' union all
select 'spnEleven', 'spnSix' union all
select 'imgFour', 'spnSix'|||SriSamp wrote:

> Check out this article from Joe Celko:
> http://www.dbazine.com/ofinterest/oi-articles/celko24
Hi SriSamp,
Thank you for the helpful link! I'll take a look at it.
Ben|||Also buy the book TREES & HIERARCHIES IN SQL; it has more details and
other methods.|||If you want to store the items in order, you might want to look at a linked
list solution.
This way you only need to edit the item after the record you're inserting.
id parent
1 <null>
2 1
3 2
4 3
insert 5 between 2 & 3.
begin trans (serializable)
insert table values ( 5 , 2 )
update table set parent = 5 where parent = 2 and id != 5
commit trans
Then you end up with
id parent
1 <null>
2 1
5 2
3 5
4 3
HTH
"Ben Amada" <ben@.REpoMOweVErpick.com> wrote in message
news:OL%23BJww0FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a requirement to store hierarchical data in a SQL database. I've
> seen several different solutions posted here in the past on how to
retrieve
> hierarchical data.
> In an effort to simplify the storage and retrieval of this data, I was
> thinking of initally inserting the data into the table with some type of
> "Level" or "Position" column which indicates the position in the hierarchy
> of each item. I will need to insert and remove items in the hierarchy
> somewhat frequently.
> For an insertion into the hierarchy, if I know where the new item's
position
> should be, I could just insert the new item into the table with that
> position number, then increment the item which previously had that
> particular position and all the items below it by one. Removing an item
> from the hierarchy would follow similar logic.
> Are there any drawbacks to storing the hierarchical data this way? Is
there
> any reason for me to avoid this method of maintaining a "Position" column
> for each item?
> Any commenets are appreciated,
> Ben
>