I'm looking for a way to ensure a value of a column in an insert or update is unique over multiple columns. For example, in this table
accounts
id varchar(32)
readkey varchar(16)
writekey varchar(16)
I want to ensure that when a row of accounts is inserted or updated that the union of all readkey values and writekey values contains no duplicates.
I know some "hard" ways to do this (like a second table of all keys, or a trigger that tests new readkey values against all readkey and writekey values, and likewise new writekey values against all readkey and writekey values, and so on). But I'm betting that savvy SQL folks know a better way. In case it matters, I'm using IBM DB2 8.1.
Thanks
Billcreate a composite key with unique attribute.
not sure if it will work in DB2 though|||Thanks but that doesn't do it. I'm not trying to ensure that no combination of readkey || writekey ever occurs twice. I need to ensure that no readkey is the same as any other readkey or writekey, and no other writekey is the same as any other readkey or writekey.
Bill|||I don't know DB2. In standard SQL you can create a constraint something like:
ALTER TABLE accounts a1
ADD CONSTRAINT c1
CHECK (NOT EXISTS (SELECT NULL FROM accounts a2 WHERE a2.readkey = a1.writekey));
That, along with UNIQUE constraints on the 2 columns, would do it.
Alternatively, perhaps a Materialized View based on:
SELECT 'R' AS mode, readkey AS key FROM accounts
UNION
SELECT 'W' AS mode, writekey AS key FROM accounts
... with a unique constraint on (key).|||Thanks for the suggestions. I couldn't make either work, but the ideas in them provided a way.
The CHECK constraint was my first idea, but I learned that CHECK constraints cannot depend on values from more than one row, so the SELECT which examines the whole table will not work.
The materialized view was a good idea but doesn't work, since materialized views do not permit UNION - the query has to be a subset query.
What I did get to work was to create a (regular) VIEW using the UNION roughly as you suggested, then create triggers for insert and update that throw an error if the key already exists in the union view. The view and two triggers is more complex than I hoped, but at this point I'm happy just to have a solution.
Thanks again for the help.|||In DB2 v8 you can use a sequence object for this purpose.
Showing posts with label ensure. Show all posts
Showing posts with label ensure. Show all posts
Wednesday, March 28, 2012
How to ensure the rest of the records will be inserted even if there is an error
Hi,
I have a sql statement that perform bulk insert into another table.
How can I ensure that if an error occurs, maybe due to primary key
constraint, the rest of the records will be inserted.
Thanks alot.Hi
Take a look at this example, even this transaction will generate a violation
of primary key constraint the rest of the data will be inserted
Please read SET ARITHABORT commant in the BOL to get a whole picture
create table #t (col int not null primary key)
begin tran
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (3)
insert into #t values (4)
insert into #t values (5)
commit
select * from #t
drop table #t
"Shelby" <shelby@.singnet.com.sg> wrote in message
news:%23X6PcA6XGHA.3868@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a sql statement that perform bulk insert into another table.
> How can I ensure that if an error occurs, maybe due to primary key
> constraint, the rest of the records will be inserted.
> Thanks alot.
>
>
>|||That depends on the type of error that occures. Fatal Errrors for
example will always abort the entire procedure and rollback all
transactions. There might be a way for non-fatal errors using if-blocks
like
if @.err<>0
// do something else with the datasql
I have a sql statement that perform bulk insert into another table.
How can I ensure that if an error occurs, maybe due to primary key
constraint, the rest of the records will be inserted.
Thanks alot.Hi
Take a look at this example, even this transaction will generate a violation
of primary key constraint the rest of the data will be inserted
Please read SET ARITHABORT commant in the BOL to get a whole picture
create table #t (col int not null primary key)
begin tran
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (3)
insert into #t values (4)
insert into #t values (5)
commit
select * from #t
drop table #t
"Shelby" <shelby@.singnet.com.sg> wrote in message
news:%23X6PcA6XGHA.3868@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a sql statement that perform bulk insert into another table.
> How can I ensure that if an error occurs, maybe due to primary key
> constraint, the rest of the records will be inserted.
> Thanks alot.
>
>
>|||That depends on the type of error that occures. Fatal Errrors for
example will always abort the entire procedure and rollback all
transactions. There might be a way for non-fatal errors using if-blocks
like
if @.err<>0
// do something else with the datasql
How to ensure that connection is made through internet instead of WAN ?
I am programming a VB-SQL Server 2K application which connects the clients
to the database through the internet. While I am testing the application
within a LAN in which the database server as well as the client connect to
the internet through a router I can't ensure that the client connects to the
database through the internet. In case I disconnect the internet WAN
connection, the application still is able to work. So the connection must be
through the LAN I guess. However, after a short while, the application is
not able to work and displays error messages concerning that it could not
establish a connection, which I had expected right after the disconnection.
Any suggestion how I can ensure that the client within the LAN connects to
the server through the internet ?
In the connection I used the following code :
DB.Provider = "SQLOLEDB"
DB.Properties("Data Source").Value = "80.123.12.123,1433" '"DATASERVER"
regards,
OscarI do not believe there is anyway for SQL Server or MDAC to force a
connection over the internet.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||The connection routing is taken care of by the OS, so you must look at the
OS level procesing.
One thing that you might try is looking at your gateway configuration, if
you set the metric on
the WAN segment higher than the internet segment (if possible given your
network topology)
then the OS should pick the internet segment instead of the LAN.
Hope this helps.
Michael
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:cDOz0uJAEHA.3792@.cpmsftngxa06.phx.gbl...
> I do not believe there is anyway for SQL Server or MDAC to force a
> connection over the internet.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
to the database through the internet. While I am testing the application
within a LAN in which the database server as well as the client connect to
the internet through a router I can't ensure that the client connects to the
database through the internet. In case I disconnect the internet WAN
connection, the application still is able to work. So the connection must be
through the LAN I guess. However, after a short while, the application is
not able to work and displays error messages concerning that it could not
establish a connection, which I had expected right after the disconnection.
Any suggestion how I can ensure that the client within the LAN connects to
the server through the internet ?
In the connection I used the following code :
DB.Provider = "SQLOLEDB"
DB.Properties("Data Source").Value = "80.123.12.123,1433" '"DATASERVER"
regards,
OscarI do not believe there is anyway for SQL Server or MDAC to force a
connection over the internet.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||The connection routing is taken care of by the OS, so you must look at the
OS level procesing.
One thing that you might try is looking at your gateway configuration, if
you set the metric on
the WAN segment higher than the internet segment (if possible given your
network topology)
then the OS should pick the internet segment instead of the LAN.
Hope this helps.
Michael
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:cDOz0uJAEHA.3792@.cpmsftngxa06.phx.gbl...
> I do not believe there is anyway for SQL Server or MDAC to force a
> connection over the internet.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
How to ensure data is no longer on disk
I have a table with some sensitive customer data in it. I am now
keeping all the data in another table, and encrypting it. I want to
get rid of the original unencrypted data and be sure that it is no
longer anywhere on disk. Should I drop the table, or first delete the
rows and then do a dump tran? I'm not sure how to know if the data is
actually physically deleted from disk, or if it's still there, but
just in blocks that get marked as available. Any guidance would be
greatly appreciated.
keeping all the data in another table, and encrypting it. I want to
get rid of the original unencrypted data and be sure that it is no
longer anywhere on disk. Should I drop the table, or first delete the
rows and then do a dump tran? I'm not sure how to know if the data is
actually physically deleted from disk, or if it's still there, but
just in blocks that get marked as available. Any guidance would be
greatly appreciated.
Thanks,
Brucesandell@.pacbell.net (Bruce) wrote in message news:<595024a5.0405061512.13a1c5f1@.posting.google.com>...
> I have a table with some sensitive customer data in it. I am now
> keeping all the data in another table, and encrypting it. I want to
> get rid of the original unencrypted data and be sure that it is no
> longer anywhere on disk. Should I drop the table, or first delete the
> rows and then do a dump tran? I'm not sure how to know if the data is
> actually physically deleted from disk, or if it's still there, but
> just in blocks that get marked as available. Any guidance would be
> greatly appreciated.
> Thanks,
> Bruce
It depends how serious you are about getting rid of the unencrypted
data. You could add a new physical disk to the server, create a new
empty database on it, copy over all the data except the unencrypted
data, then remove the existing disk and format it and/or overwrite the
sectors with a suitable low-level disk tool.
If that is overkill for your needs, you could UPDATE all the
unencrypted data to something meaningless and then checkpoint the
database, which should overwrite the current pages on disk. You could
then DELETE the rows, and delete any transaction log backup files
(assuming you don't need them for recovery).
Don't forget that the unencrypted data may still exist in database
backups, so you would need to address that issue also.
Simon
How to ensure column uniqueness
Hi,
Coming from an Oracle background, I'm used to being able
to create a unique key on a column that allows many null
values, ie if a value exists it must be unique, otherwise
it can be null.
It appears as though SQL Server allows only 1 null value
in the same situation, ie create a unique constraint on a
column and once you try to insert a 2nd row with a null
value I get a constraint violation. [Thx to those that
replied to my last question on this]
I don't really want to write triggers testing for such
conditions. Is there some form of constraint that can do
this for me?
TIA,
SJTYour observations are correct. You can create a view conatining all rows but the NULL. Then create a
unique index (or possible a unique constraint) on that index.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"SJT" <scott.taylor@.pwcs.com.au> wrote in message news:054601c36ad9$46dd1f90$a501280a@.phx.gbl...
> Hi,
> Coming from an Oracle background, I'm used to being able
> to create a unique key on a column that allows many null
> values, ie if a value exists it must be unique, otherwise
> it can be null.
> It appears as though SQL Server allows only 1 null value
> in the same situation, ie create a unique constraint on a
> column and once you try to insert a 2nd row with a null
> value I get a constraint violation. [Thx to those that
> replied to my last question on this]
> I don't really want to write triggers testing for such
> conditions. Is there some form of constraint that can do
> this for me?
>
> TIA,
> SJT|||You can use an indexed view to enforce uniqueness only for non-NULL values:
CREATE TABLE Sometable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL)
GO
CREATE VIEW Sometable_Unique_Non_NULL
WITH SCHEMABINDING
AS SELECT colx FROM dbo.Sometable WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON Sometable_Unique_Non_NULL (colx)
INSERT INTO Sometable VALUES (1,1)
INSERT INTO Sometable VALUES (2,NULL)
INSERT INTO Sometable VALUES (3,NULL)
--
David Portas
--
Please reply only to the newsgroup
--
Coming from an Oracle background, I'm used to being able
to create a unique key on a column that allows many null
values, ie if a value exists it must be unique, otherwise
it can be null.
It appears as though SQL Server allows only 1 null value
in the same situation, ie create a unique constraint on a
column and once you try to insert a 2nd row with a null
value I get a constraint violation. [Thx to those that
replied to my last question on this]
I don't really want to write triggers testing for such
conditions. Is there some form of constraint that can do
this for me?
TIA,
SJTYour observations are correct. You can create a view conatining all rows but the NULL. Then create a
unique index (or possible a unique constraint) on that index.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"SJT" <scott.taylor@.pwcs.com.au> wrote in message news:054601c36ad9$46dd1f90$a501280a@.phx.gbl...
> Hi,
> Coming from an Oracle background, I'm used to being able
> to create a unique key on a column that allows many null
> values, ie if a value exists it must be unique, otherwise
> it can be null.
> It appears as though SQL Server allows only 1 null value
> in the same situation, ie create a unique constraint on a
> column and once you try to insert a 2nd row with a null
> value I get a constraint violation. [Thx to those that
> replied to my last question on this]
> I don't really want to write triggers testing for such
> conditions. Is there some form of constraint that can do
> this for me?
>
> TIA,
> SJT|||You can use an indexed view to enforce uniqueness only for non-NULL values:
CREATE TABLE Sometable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL)
GO
CREATE VIEW Sometable_Unique_Non_NULL
WITH SCHEMABINDING
AS SELECT colx FROM dbo.Sometable WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON Sometable_Unique_Non_NULL (colx)
INSERT INTO Sometable VALUES (1,1)
INSERT INTO Sometable VALUES (2,NULL)
INSERT INTO Sometable VALUES (3,NULL)
--
David Portas
--
Please reply only to the newsgroup
--
Friday, March 23, 2012
How to enable SSL encryption as BOTH client and server
We have an application that requires bi-directional data transfer between 2
SQL servers. To ensure the data transfer is encrypted, we
are considering to use SQL server SSL encryption. However, the
problem is for this case, each machine is acting as both client and
server for SSL encryption. So is there any way to do this? As far as
I know, we can turn on this "enforce protocle encryption" option
on either client or server, but not both. Besides, will this mean
we need to install certificate on every machine?
If this is not possible in SQL server, is there any other way
to achieve the same goal?
Thanks.
The protocol encryption requires the use of certificates.
Read this if you're unfamiliar with PKI
http://www.microsoft.com/technet/arc...ate/featfunc/p
kiintro.mspx
If you want encryption going both directions then enable it on the
serverside.
This would require certificates on both machines. If the certificates are
issued by the same
CA, then it won't be a problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi Kevin:
Thanks a lot for the information. I have tried the following and still have
some problems:
I install the certificates on both machines.
I turn on the "server" encryption on machine-1.
I turn on the "client" encryption on machine-1 too.
Start my application, cannot connect to the database - from either machine.
However, if I only turn on the "server" encryption on machine-1,
then application works.
My question is, in this case, I think the connection from machine-2 to
machine-1 is encrypted, but does the connection from machine-1
to machine-2 also encrypted?
Yuh-Ming
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:roYhgmXKEHA.928@.cpmsftngxa10.phx.gbl...
> The protocol encryption requires the use of certificates.
> Read this if you're unfamiliar with PKI
>
http://www.microsoft.com/technet/arc...ate/featfunc/p
> kiintro.mspx
> If you want encryption going both directions then enable it on the
> serverside.
> This would require certificates on both machines. If the certificates are
> issued by the same
> CA, then it won't be a problem.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Turning on encryption at the server 1 allows all communication to be
encrypted.
There's no need to enable encryption on the client as well.
Turn on encryption at both servers using the server network utility and I
think you'll have what
you need.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
SQL servers. To ensure the data transfer is encrypted, we
are considering to use SQL server SSL encryption. However, the
problem is for this case, each machine is acting as both client and
server for SSL encryption. So is there any way to do this? As far as
I know, we can turn on this "enforce protocle encryption" option
on either client or server, but not both. Besides, will this mean
we need to install certificate on every machine?
If this is not possible in SQL server, is there any other way
to achieve the same goal?
Thanks.
The protocol encryption requires the use of certificates.
Read this if you're unfamiliar with PKI
http://www.microsoft.com/technet/arc...ate/featfunc/p
kiintro.mspx
If you want encryption going both directions then enable it on the
serverside.
This would require certificates on both machines. If the certificates are
issued by the same
CA, then it won't be a problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi Kevin:
Thanks a lot for the information. I have tried the following and still have
some problems:
I install the certificates on both machines.
I turn on the "server" encryption on machine-1.
I turn on the "client" encryption on machine-1 too.
Start my application, cannot connect to the database - from either machine.
However, if I only turn on the "server" encryption on machine-1,
then application works.
My question is, in this case, I think the connection from machine-2 to
machine-1 is encrypted, but does the connection from machine-1
to machine-2 also encrypted?
Yuh-Ming
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:roYhgmXKEHA.928@.cpmsftngxa10.phx.gbl...
> The protocol encryption requires the use of certificates.
> Read this if you're unfamiliar with PKI
>
http://www.microsoft.com/technet/arc...ate/featfunc/p
> kiintro.mspx
> If you want encryption going both directions then enable it on the
> serverside.
> This would require certificates on both machines. If the certificates are
> issued by the same
> CA, then it won't be a problem.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Turning on encryption at the server 1 allows all communication to be
encrypted.
There's no need to enable encryption on the client as well.
Turn on encryption at both servers using the server network utility and I
think you'll have what
you need.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
How to enable SSL encryption as BOTH client and server
We have an application that requires bi-directional data transfer between 2
SQL servers. To ensure the data transfer is encrypted, we
are considering to use SQL server SSL encryption. However, the
problem is for this case, each machine is acting as both client and
server for SSL encryption. So is there any way to do this? As far as
I know, we can turn on this "enforce protocle encryption" option
on either client or server, but not both. Besides, will this mean
we need to install certificate on every machine?
If this is not possible in SQL server, is there any other way
to achieve the same goal?
Thanks.The protocol encryption requires the use of certificates.
Read this if you're unfamiliar with PKI
http://www.microsoft.com/technet/ar...uate/featfunc/p
kiintro.mspx
If you want encryption going both directions then enable it on the
serverside.
This would require certificates on both machines. If the certificates are
issued by the same
CA, then it won't be a problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin:
Thanks a lot for the information. I have tried the following and still have
some problems:
I install the certificates on both machines.
I turn on the "server" encryption on machine-1.
I turn on the "client" encryption on machine-1 too.
Start my application, cannot connect to the database - from either machine.
However, if I only turn on the "server" encryption on machine-1,
then application works.
My question is, in this case, I think the connection from machine-2 to
machine-1 is encrypted, but does the connection from machine-1
to machine-2 also encrypted?
Yuh-Ming
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:roYhgmXKEHA.928@.cpmsftngxa10.phx.gbl...
> The protocol encryption requires the use of certificates.
> Read this if you're unfamiliar with PKI
>
http://www.microsoft.com/technet/ar...uate/featfunc/p
> kiintro.mspx
> If you want encryption going both directions then enable it on the
> serverside.
> This would require certificates on both machines. If the certificates are
> issued by the same
> CA, then it won't be a problem.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Turning on encryption at the server 1 allows all communication to be
encrypted.
There's no need to enable encryption on the client as well.
Turn on encryption at both servers using the server network utility and I
think you'll have what
you need.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
SQL servers. To ensure the data transfer is encrypted, we
are considering to use SQL server SSL encryption. However, the
problem is for this case, each machine is acting as both client and
server for SSL encryption. So is there any way to do this? As far as
I know, we can turn on this "enforce protocle encryption" option
on either client or server, but not both. Besides, will this mean
we need to install certificate on every machine?
If this is not possible in SQL server, is there any other way
to achieve the same goal?
Thanks.The protocol encryption requires the use of certificates.
Read this if you're unfamiliar with PKI
http://www.microsoft.com/technet/ar...uate/featfunc/p
kiintro.mspx
If you want encryption going both directions then enable it on the
serverside.
This would require certificates on both machines. If the certificates are
issued by the same
CA, then it won't be a problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin:
Thanks a lot for the information. I have tried the following and still have
some problems:
I install the certificates on both machines.
I turn on the "server" encryption on machine-1.
I turn on the "client" encryption on machine-1 too.
Start my application, cannot connect to the database - from either machine.
However, if I only turn on the "server" encryption on machine-1,
then application works.
My question is, in this case, I think the connection from machine-2 to
machine-1 is encrypted, but does the connection from machine-1
to machine-2 also encrypted?
Yuh-Ming
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:roYhgmXKEHA.928@.cpmsftngxa10.phx.gbl...
> The protocol encryption requires the use of certificates.
> Read this if you're unfamiliar with PKI
>
http://www.microsoft.com/technet/ar...uate/featfunc/p
> kiintro.mspx
> If you want encryption going both directions then enable it on the
> serverside.
> This would require certificates on both machines. If the certificates are
> issued by the same
> CA, then it won't be a problem.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Turning on encryption at the server 1 allows all communication to be
encrypted.
There's no need to enable encryption on the client as well.
Turn on encryption at both servers using the server network utility and I
think you'll have what
you need.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Wednesday, March 21, 2012
How to enable 2-way SSL encryption
We have an application that requires bi-directional data transfer between 2
SQL servers. To ensure the data transfer is encrypted, we
are considering to use SQL server SSL encryption. However, the
problem is for this case, each machine is acting as both client and
server for SSL encryption. So is there any way to do this? As far as
I know, we can turn on this "enforce protocle encryption" option
on either client or server, but not both. Besides, will this mean
we need to install certificate on every machine?
If this is not possible in SQL server, is there any other way
to achieve the same goal?
Thanks.
I suggest you look into ipsec (IPSecurity protocol).
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Yuh-MIng Shyy" <yshyy@.imedica.com> wrote in message
news:Wydic.2$C02.1637@.news.nyc.globix.net...
> We have an application that requires bi-directional data transfer between
2
> SQL servers. To ensure the data transfer is encrypted, we
> are considering to use SQL server SSL encryption. However, the
> problem is for this case, each machine is acting as both client and
> server for SSL encryption. So is there any way to do this? As far as
> I know, we can turn on this "enforce protocle encryption" option
> on either client or server, but not both. Besides, will this mean
> we need to install certificate on every machine?
> If this is not possible in SQL server, is there any other way
> to achieve the same goal?
> Thanks.
>
>
|||Hi Yuh-Mlng,
From your description, I understand that you would like to know something
about SSL encryption and connection between two machines.
Based on my knowledge, authentication is provided through the use of a
digital signature. This digital signature takes the form of a certificate
which is administered from a Certificate Authority. For more information,
there is a good blurb in article q205698, "Submit a certificate request to
this CA using a form." Another good article is q245152, "How Secure
Sockets Layer Works."
SQL server 2000 implements SSL. There are two main setup procedures to
implement SSL:
Enable SSL on the SQL Server - following this procedure results in all
client
connections to SQL Server implementing SSL. This requires two steps:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Enable Force protocol encryption in the SQL Server Network Utility.
Enable SSL on individual clients - following this procedure results in
implenting secure connections between SQL Server and only those clients
configured for secure connections. Example - you have approx 100 clients
that connect to SQL 2000 server, but you only require two connections be
secure. This is a good example of where client configured secure sockets
may be arguably a better, more efficient implementation. To set up client
SSL to SQL Server:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Set up client with Trusted Root CA certificate - basically
certificate so
that client trusts the CA that gave SQL Server its certificate.
3. On the client, enable "Force protocol encryption in the SQL Server
Client
Network Utility.
NOTICE that force protocol encryption may have some performance impact on
your SQL Server in some cases.
Moreover, you could have a look at
INF: How SQL Server Uses a Certificate When the Force Protocol Encryption
Option is Set On
http://support.microsoft.com/?id=318605
HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
http://support.microsoft.com/?id=276553
HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft Management
Console
http://support.microsoft.com/?id=316898
In addition, if you are unfamiliar with PKI, I would like to recommand the
documents below
An Introduction to the Windows 2000 Public-Key Infrastructure
http://www.microsoft.com/technet/arc...ate/featfunc/p
kiintro.mspx
which will give you a brief introduction for Windows 2000 PKI
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
sql
SQL servers. To ensure the data transfer is encrypted, we
are considering to use SQL server SSL encryption. However, the
problem is for this case, each machine is acting as both client and
server for SSL encryption. So is there any way to do this? As far as
I know, we can turn on this "enforce protocle encryption" option
on either client or server, but not both. Besides, will this mean
we need to install certificate on every machine?
If this is not possible in SQL server, is there any other way
to achieve the same goal?
Thanks.
I suggest you look into ipsec (IPSecurity protocol).
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Yuh-MIng Shyy" <yshyy@.imedica.com> wrote in message
news:Wydic.2$C02.1637@.news.nyc.globix.net...
> We have an application that requires bi-directional data transfer between
2
> SQL servers. To ensure the data transfer is encrypted, we
> are considering to use SQL server SSL encryption. However, the
> problem is for this case, each machine is acting as both client and
> server for SSL encryption. So is there any way to do this? As far as
> I know, we can turn on this "enforce protocle encryption" option
> on either client or server, but not both. Besides, will this mean
> we need to install certificate on every machine?
> If this is not possible in SQL server, is there any other way
> to achieve the same goal?
> Thanks.
>
>
|||Hi Yuh-Mlng,
From your description, I understand that you would like to know something
about SSL encryption and connection between two machines.
Based on my knowledge, authentication is provided through the use of a
digital signature. This digital signature takes the form of a certificate
which is administered from a Certificate Authority. For more information,
there is a good blurb in article q205698, "Submit a certificate request to
this CA using a form." Another good article is q245152, "How Secure
Sockets Layer Works."
SQL server 2000 implements SSL. There are two main setup procedures to
implement SSL:
Enable SSL on the SQL Server - following this procedure results in all
client
connections to SQL Server implementing SSL. This requires two steps:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Enable Force protocol encryption in the SQL Server Network Utility.
Enable SSL on individual clients - following this procedure results in
implenting secure connections between SQL Server and only those clients
configured for secure connections. Example - you have approx 100 clients
that connect to SQL 2000 server, but you only require two connections be
secure. This is a good example of where client configured secure sockets
may be arguably a better, more efficient implementation. To set up client
SSL to SQL Server:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Set up client with Trusted Root CA certificate - basically
certificate so
that client trusts the CA that gave SQL Server its certificate.
3. On the client, enable "Force protocol encryption in the SQL Server
Client
Network Utility.
NOTICE that force protocol encryption may have some performance impact on
your SQL Server in some cases.
Moreover, you could have a look at
INF: How SQL Server Uses a Certificate When the Force Protocol Encryption
Option is Set On
http://support.microsoft.com/?id=318605
HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
http://support.microsoft.com/?id=276553
HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft Management
Console
http://support.microsoft.com/?id=316898
In addition, if you are unfamiliar with PKI, I would like to recommand the
documents below
An Introduction to the Windows 2000 Public-Key Infrastructure
http://www.microsoft.com/technet/arc...ate/featfunc/p
kiintro.mspx
which will give you a brief introduction for Windows 2000 PKI
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
sql
How to enable 2-way SSL encryption
We have an application that requires bi-directional data transfer between 2
SQL servers. To ensure the data transfer is encrypted, we
are considering to use SQL server SSL encryption. However, the
problem is for this case, each machine is acting as both client and
server for SSL encryption. So is there any way to do this? As far as
I know, we can turn on this "enforce protocle encryption" option
on either client or server, but not both. Besides, will this mean
we need to install certificate on every machine?
If this is not possible in SQL server, is there any other way
to achieve the same goal?
Thanks.I suggest you look into ipsec (IPSecurity protocol).
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Yuh-MIng Shyy" <yshyy@.imedica.com> wrote in message
news:Wydic.2$C02.1637@.news.nyc.globix.net...
> We have an application that requires bi-directional data transfer between
2
> SQL servers. To ensure the data transfer is encrypted, we
> are considering to use SQL server SSL encryption. However, the
> problem is for this case, each machine is acting as both client and
> server for SSL encryption. So is there any way to do this? As far as
> I know, we can turn on this "enforce protocle encryption" option
> on either client or server, but not both. Besides, will this mean
> we need to install certificate on every machine?
> If this is not possible in SQL server, is there any other way
> to achieve the same goal?
> Thanks.
>
>|||Hi Yuh-Mlng,
From your description, I understand that you would like to know something
about SSL encryption and connection between two machines.
Based on my knowledge, authentication is provided through the use of a
digital signature. This digital signature takes the form of a certificate
which is administered from a Certificate Authority. For more information,
there is a good blurb in article q205698, "Submit a certificate request to
this CA using a form." Another good article is q245152, "How Secure
Sockets Layer Works."
SQL server 2000 implements SSL. There are two main setup procedures to
implement SSL:
Enable SSL on the SQL Server - following this procedure results in all
client
connections to SQL Server implementing SSL. This requires two steps:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Enable Force protocol encryption in the SQL Server Network Utility.
Enable SSL on individual clients - following this procedure results in
implenting secure connections between SQL Server and only those clients
configured for secure connections. Example - you have approx 100 clients
that connect to SQL 2000 server, but you only require two connections be
secure. This is a good example of where client configured secure sockets
may be arguably a better, more efficient implementation. To set up client
SSL to SQL Server:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Set up client with Trusted Root CA certificate - basically
certificate so
that client trusts the CA that gave SQL Server its certificate.
3. On the client, enable "Force protocol encryption in the SQL Server
Client
Network Utility.
NOTICE that force protocol encryption may have some performance impact on
your SQL Server in some cases.
Moreover, you could have a look at
INF: How SQL Server Uses a Certificate When the Force Protocol Encryption
Option is Set On
http://support.microsoft.com/?id=318605
HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
http://support.microsoft.com/?id=276553
HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft Management
Console
http://support.microsoft.com/?id=316898
In addition, if you are unfamiliar with PKI, I would like to recommand the
documents below
An Introduction to the Windows 2000 Public-Key Infrastructure
http://www.microsoft.com/technet/ar...uate/featfunc/p
kiintro.mspx
which will give you a brief introduction for Windows 2000 PKI
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
SQL servers. To ensure the data transfer is encrypted, we
are considering to use SQL server SSL encryption. However, the
problem is for this case, each machine is acting as both client and
server for SSL encryption. So is there any way to do this? As far as
I know, we can turn on this "enforce protocle encryption" option
on either client or server, but not both. Besides, will this mean
we need to install certificate on every machine?
If this is not possible in SQL server, is there any other way
to achieve the same goal?
Thanks.I suggest you look into ipsec (IPSecurity protocol).
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Yuh-MIng Shyy" <yshyy@.imedica.com> wrote in message
news:Wydic.2$C02.1637@.news.nyc.globix.net...
> We have an application that requires bi-directional data transfer between
2
> SQL servers. To ensure the data transfer is encrypted, we
> are considering to use SQL server SSL encryption. However, the
> problem is for this case, each machine is acting as both client and
> server for SSL encryption. So is there any way to do this? As far as
> I know, we can turn on this "enforce protocle encryption" option
> on either client or server, but not both. Besides, will this mean
> we need to install certificate on every machine?
> If this is not possible in SQL server, is there any other way
> to achieve the same goal?
> Thanks.
>
>|||Hi Yuh-Mlng,
From your description, I understand that you would like to know something
about SSL encryption and connection between two machines.
Based on my knowledge, authentication is provided through the use of a
digital signature. This digital signature takes the form of a certificate
which is administered from a Certificate Authority. For more information,
there is a good blurb in article q205698, "Submit a certificate request to
this CA using a form." Another good article is q245152, "How Secure
Sockets Layer Works."
SQL server 2000 implements SSL. There are two main setup procedures to
implement SSL:
Enable SSL on the SQL Server - following this procedure results in all
client
connections to SQL Server implementing SSL. This requires two steps:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Enable Force protocol encryption in the SQL Server Network Utility.
Enable SSL on individual clients - following this procedure results in
implenting secure connections between SQL Server and only those clients
configured for secure connections. Example - you have approx 100 clients
that connect to SQL 2000 server, but you only require two connections be
secure. This is a good example of where client configured secure sockets
may be arguably a better, more efficient implementation. To set up client
SSL to SQL Server:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Set up client with Trusted Root CA certificate - basically
certificate so
that client trusts the CA that gave SQL Server its certificate.
3. On the client, enable "Force protocol encryption in the SQL Server
Client
Network Utility.
NOTICE that force protocol encryption may have some performance impact on
your SQL Server in some cases.
Moreover, you could have a look at
INF: How SQL Server Uses a Certificate When the Force Protocol Encryption
Option is Set On
http://support.microsoft.com/?id=318605
HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
http://support.microsoft.com/?id=276553
HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft Management
Console
http://support.microsoft.com/?id=316898
In addition, if you are unfamiliar with PKI, I would like to recommand the
documents below
An Introduction to the Windows 2000 Public-Key Infrastructure
http://www.microsoft.com/technet/ar...uate/featfunc/p
kiintro.mspx
which will give you a brief introduction for Windows 2000 PKI
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
How to enable 2-way SSL encryption
We have an application that requires bi-directional data transfer between 2
SQL servers. To ensure the data transfer is encrypted, we
are considering to use SQL server SSL encryption. However, the
problem is for this case, each machine is acting as both client and
server for SSL encryption. So is there any way to do this? As far as
I know, we can turn on this "enforce protocle encryption" option
on either client or server, but not both. Besides, will this mean
we need to install certificate on every machine?
If this is not possible in SQL server, is there any other way
to achieve the same goal?
Thanks.I suggest you look into ipsec (IPSecurity protocol).
--
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Yuh-MIng Shyy" <yshyy@.imedica.com> wrote in message
news:Wydic.2$C02.1637@.news.nyc.globix.net...
> We have an application that requires bi-directional data transfer between
2
> SQL servers. To ensure the data transfer is encrypted, we
> are considering to use SQL server SSL encryption. However, the
> problem is for this case, each machine is acting as both client and
> server for SSL encryption. So is there any way to do this? As far as
> I know, we can turn on this "enforce protocle encryption" option
> on either client or server, but not both. Besides, will this mean
> we need to install certificate on every machine?
> If this is not possible in SQL server, is there any other way
> to achieve the same goal?
> Thanks.
>
>|||Hi Yuh-Mlng,
From your description, I understand that you would like to know something
about SSL encryption and connection between two machines.
Based on my knowledge, authentication is provided through the use of a
digital signature. This digital signature takes the form of a certificate
which is administered from a Certificate Authority. For more information,
there is a good blurb in article q205698, "Submit a certificate request to
this CA using a form." Another good article is q245152, "How Secure
Sockets Layer Works."
SQL server 2000 implements SSL. There are two main setup procedures to
implement SSL:
Enable SSL on the SQL Server - following this procedure results in all
client
connections to SQL Server implementing SSL. This requires two steps:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Enable Force protocol encryption in the SQL Server Network Utility.
Enable SSL on individual clients - following this procedure results in
implenting secure connections between SQL Server and only those clients
configured for secure connections. Example - you have approx 100 clients
that connect to SQL 2000 server, but you only require two connections be
secure. This is a good example of where client configured secure sockets
may be arguably a better, more efficient implementation. To set up client
SSL to SQL Server:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Set up client with Trusted Root CA certificate - basically
certificate so
that client trusts the CA that gave SQL Server its certificate.
3. On the client, enable "Force protocol encryption in the SQL Server
Client
Network Utility.
NOTICE that force protocol encryption may have some performance impact on
your SQL Server in some cases.
Moreover, you could have a look at
INF: How SQL Server Uses a Certificate When the Force Protocol Encryption
Option is Set On
http://support.microsoft.com/?id=318605
HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
http://support.microsoft.com/?id=276553
HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft Management
Console
http://support.microsoft.com/?id=316898
In addition, if you are unfamiliar with PKI, I would like to recommand the
documents below
An Introduction to the Windows 2000 Public-Key Infrastructure
http://www.microsoft.com/technet/archive/windows2000serv/evaluate/featfunc/p
kiintro.mspx
which will give you a brief introduction for Windows 2000 PKI
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
SQL servers. To ensure the data transfer is encrypted, we
are considering to use SQL server SSL encryption. However, the
problem is for this case, each machine is acting as both client and
server for SSL encryption. So is there any way to do this? As far as
I know, we can turn on this "enforce protocle encryption" option
on either client or server, but not both. Besides, will this mean
we need to install certificate on every machine?
If this is not possible in SQL server, is there any other way
to achieve the same goal?
Thanks.I suggest you look into ipsec (IPSecurity protocol).
--
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Yuh-MIng Shyy" <yshyy@.imedica.com> wrote in message
news:Wydic.2$C02.1637@.news.nyc.globix.net...
> We have an application that requires bi-directional data transfer between
2
> SQL servers. To ensure the data transfer is encrypted, we
> are considering to use SQL server SSL encryption. However, the
> problem is for this case, each machine is acting as both client and
> server for SSL encryption. So is there any way to do this? As far as
> I know, we can turn on this "enforce protocle encryption" option
> on either client or server, but not both. Besides, will this mean
> we need to install certificate on every machine?
> If this is not possible in SQL server, is there any other way
> to achieve the same goal?
> Thanks.
>
>|||Hi Yuh-Mlng,
From your description, I understand that you would like to know something
about SSL encryption and connection between two machines.
Based on my knowledge, authentication is provided through the use of a
digital signature. This digital signature takes the form of a certificate
which is administered from a Certificate Authority. For more information,
there is a good blurb in article q205698, "Submit a certificate request to
this CA using a form." Another good article is q245152, "How Secure
Sockets Layer Works."
SQL server 2000 implements SSL. There are two main setup procedures to
implement SSL:
Enable SSL on the SQL Server - following this procedure results in all
client
connections to SQL Server implementing SSL. This requires two steps:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Enable Force protocol encryption in the SQL Server Network Utility.
Enable SSL on individual clients - following this procedure results in
implenting secure connections between SQL Server and only those clients
configured for secure connections. Example - you have approx 100 clients
that connect to SQL 2000 server, but you only require two connections be
secure. This is a good example of where client configured secure sockets
may be arguably a better, more efficient implementation. To set up client
SSL to SQL Server:
1. Create a valid certificate from a Certificate Authority on the SQL
Server
2. Set up client with Trusted Root CA certificate - basically
certificate so
that client trusts the CA that gave SQL Server its certificate.
3. On the client, enable "Force protocol encryption in the SQL Server
Client
Network Utility.
NOTICE that force protocol encryption may have some performance impact on
your SQL Server in some cases.
Moreover, you could have a look at
INF: How SQL Server Uses a Certificate When the Force Protocol Encryption
Option is Set On
http://support.microsoft.com/?id=318605
HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
http://support.microsoft.com/?id=276553
HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft Management
Console
http://support.microsoft.com/?id=316898
In addition, if you are unfamiliar with PKI, I would like to recommand the
documents below
An Introduction to the Windows 2000 Public-Key Infrastructure
http://www.microsoft.com/technet/archive/windows2000serv/evaluate/featfunc/p
kiintro.mspx
which will give you a brief introduction for Windows 2000 PKI
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
Subscribe to:
Posts (Atom)