Change how command runs

This should be an easy one. I want to change the command to use the
DBID number instead of the database name to run the process. The
current loop does not keep the order if you add or remove databases
beecuse i am using the min() function.
DECLARE @.sql varchar(4000)
DECLARE @.db varchar(64)
SET @.db=''
SELECT @.db=min(name)
FROM master.dbo.sysdatabases
WHERE dbid > 4 and name > @.db
WHILE @.db is not null
BEGIN
SET @.sql='use ' +@.db + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
EXEC (@.sql)
SELECT @.db=min(name)
FROM master.dbo.sysdatabases
WHERE dbid > 4 and name > @.db
END
Thanks
-Matt-not sure what are you exactly looking for.
but if you want to loop through the databases on the basis of DBID rather
than database name , then you can try following procedural code.
DECLARE @.sql varchar(4000)
DECLARE @.db int
SELECT @.db=min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.db is not null
BEGIN
select @.sql='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
from master..sysdatabases where dbid =@.db
EXEC (@.sql)
SELECT @.db=min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.db
END
"MKruer@.gmail.com" wrote:

> This should be an easy one. I want to change the command to use the
> DBID number instead of the database name to run the process. The
> current loop does not keep the order if you add or remove databases
> beecuse i am using the min() function.
> DECLARE @.sql varchar(4000)
> DECLARE @.db varchar(64)
> SET @.db=''
> SELECT @.db=min(name)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4 and name > @.db
> WHILE @.db is not null
> BEGIN
> SET @.sql='use ' +@.db + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
> EXEC (@.sql)
> SELECT @.db=min(name)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4 and name > @.db
> END
> Thanks
> -Matt-
>|||That was it. The Query is to run a stored procedure that auto reindex
the entire Database. The only problem with the stored procedure is that
it only works on the database that it was executed on. This is more or
a temporary work around. Ideally I want to be able to specify
individual indexes directly using a table.
Creating a Stored Procedure to collect DB information and then run
another Stored Procedure using that information
http://groups.google.com/group/micr...bdb43d49fed239e
Query for Database, Table, Index name all on one list.
http://groups.google.com/group/micr...17098a35404820c|||That was it. The Query is to run a stored procedure that auto reindex
the entire Database. The only problem with the stored procedure is that
it only works on the database that it was executed on. This is more or
a temporary work around. Ideally I want to be able to specify
individual indexes directly using a table.
Creating a Stored Procedure to collect DB information and then run
another Stored Procedure using that information
http://groups.google.com/group/micr...bdb43d49fed239e
Query for Database, Table, Index name all on one list.
http://groups.google.com/group/micr...17098a35404820csql

Change Data Capture

Hi all,

I have studied on the Change Data Capture in Katmai. That is new with Katmai. I wonder how it is implemented actually. Does it use a process like service broker? I hope it does not affect the performance of the ongoing processes or transactions on the database engine.

I worked on a sample on my Katmai installation and took my notes at http://www.kodyaz.com/articles/change-data-capture.aspx

I find the tables and functions a little bit confusing. But after working on a sample it makes it easier to understand the structure.

With the preliminary information I have now this is retrieved from the transaction log.

The 'inside' information is not yet available I'm afraid :-) There is a nice schema in the BOL though.

Everything has a cost. The question is: "is the performance impact acceptable on my environment" and this can only be determined by tests on your specific environment.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||The CDC will definitely add some extra disk activity to the system, and thus I wonder if there will be an option to specify a filegroup to place the CDC data on, to improve performance of course.|||

Yes, you have control at the physical level. The only thing I don't like at all is the way to query it. It exposes the guts of the system at the logical level. This is all fine for debugging (as a DBA, access to the LSN is a goodie) but it should not be exposed to query/develop againts it. Mixing the logical and the physical is bad design.

change configuration notification process

I need to implement an automated change configuration notification process
for all my current SQL server configurations. I don't know if SQL-DMO is the
way to go here, but I certainly do not know enough about it to be proficient.
Any web links and info would be greatly appreciated.
All SQL boxes are of the 2000 variety.
SQLcat,
Could you define your requirements more clearly? What do you mean by
"automated change configuration notification process". What changes do you
need to monitor? Schema, security, data, etc?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"SQLcat" wrote:

> I need to implement an automated change configuration notification process
> for all my current SQL server configurations. I don't know if SQL-DMO is the
> way to go here, but I certainly do not know enough about it to be proficient.
> Any web links and info would be greatly appreciated.
> All SQL boxes are of the 2000 variety.
|||sorry...I'm interested in ensuring the server side configuration settings
remain the same across the board and that if anyone other than myself has
made a change, I'd be notified...perhaps this would fall under security?
"Mark Allison" wrote:
[vbcol=seagreen]
> SQLcat,
> Could you define your requirements more clearly? What do you mean by
> "automated change configuration notification process". What changes do you
> need to monitor? Schema, security, data, etc?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
>
> "SQLcat" wrote:
|||in thinking about this a little further, I guess if I had the results of
sp_configure for each SQL server in my environment, I could compare the
results daily to see if any server side changes occurred....while it would
be nice to determine who would've changed what, the aforementioned will be a
good start....how could I get something like sp_configure to give me server
side results for all my SQL boxes without having to go to each box and run it
in QA?
"Mark Allison" wrote:
[vbcol=seagreen]
> SQLcat,
> Could you define your requirements more clearly? What do you mean by
> "automated change configuration notification process". What changes do you
> need to monitor? Schema, security, data, etc?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
>
> "SQLcat" wrote:

Chain of triggers - how to break it?

I have an application to capture and process timesheet information.
Put simply, employees clock in and out at various locations. Business
logic determines how to process these events.

The system allows an administrator (though it's not strictly relevant,
this is through an ASP.NET front end) to determine "rules" for
employees. A rule will consist of a Rostered Start Time (the time that
an employee or group of employees is expected to "clock on"), a
threshold allowance for early and late starts, and a Minimum and a
Maximum number of minutes that an employee is expected to work. This
table is called tblTSRule (see DDL below).

Another part of the system assigns a rule to one or more employees.
The table that stores this data is called tblTSEmpRules. The columns
in this table contain links to the tblTSRule table, the tblEmployee
table, the date for which this rule applies, and the two fields that
are at the heart of this question. These are fldLowerBound and
fldUpperBound.

All employee timesheet entries (which are via a barcode scan on their
id badge) are simply raw data - the system captures the EmployeeID, the
location and the time of the scan. It doesn't differentiate between a
"clock on", a "clock off" or a "sub duty" (where an employee has left
their normal place of work to do a rostered duty at another location).
These raw "scans" are processed en bloc at a later date. To
illustrate, the UI presents the user with a range of dates, and the
user can then "apply the rules" to a date or range of dates.

At this point the system needs to collate all the raw scans according
to the date that they wish to process. However, let's say that the
date is the 31st January, but some of the employees for that date are
working a late shift. Logically, therefore, some of the relevant scans
will actually occur on 1 February.

In order to get all the relevant scans for each employee, therefore,
the "rule" for an employee for any particular date will contain the
Lower and Upper bounds between which all raw scans should be processed.
We found that with a typical data load of around 1,200,000 records
that processing was very slow if the lower and upper bounds were
calculated "on the fly" in the SQL, so instead we decided to store this
calculated data in the table itself. Codd may not like it, but it's
expedient.

The two fields fldLowerBound and fldUpperBound are calculated or
recalculated EITHER when a row in the tblTSRule table is updated or
when rows in the tblTSEmpRules table are either inserted or updated.
This is done via INSERT and UPDATE triggers. There is no requirement
to have an INSERT trigger on the tblTSRule, since creating a new rule
automatically removes the possibility that there are any related rows
in the tblTSEmpRules table. The triggers are scripted below.

What we are finding is that if an update is made on the tblTSRule
table, it fires TWO triggers - first the UPDATE trigger on the
tblTSRule table (which updates the tblTSEmpRules table) and then the
UPDATE trigger on the tblTSEmpRules table. The two triggers are
virtually identical, but what can we do in terms of design to get
around this? I should point out that the ability exists in the
application to amend both rules and the assignment of rules to
employees.

Thank you for reading this far. DDL below.

Edward

========================

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSEmpRules_tblTSRules]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSEmpRules] DROP CONSTRAINT
FK_tblTSEmpRules_tblTSRules
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CreateEmpCaptureBound]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[CreateEmpCaptureBound]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CreateCaptureBound]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[CreateCaptureBound]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[UpdateCaptureBound]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[UpdateCaptureBound]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSEmpRules]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblTSEmpRules]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSRule]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblTSRule]
GO

CREATE TABLE [dbo].[tblTSEmpRules] (
[fldEmpRuleID] [int] IDENTITY (1, 1) NOT NULL ,
[fldDate] [datetime] NULL ,
[fldEmployeeID] [int] NULL ,
[fldRuleID] [int] NULL ,
[fldLowerBound] [datetime] NOT NULL ,
[fldUpperBound] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTSRule] (
[fldRuleID] [int] IDENTITY (1, 1) NOT NULL ,
[fldCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[fldDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldRosteredStart] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[fldEarlyStartArb] [int] NULL ,
[fldLateStartArb] [int] NULL ,
[fldMinMins] [int] NULL ,
[fldMaxMins] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSEmpRules] WITH NOCHECK ADD
CONSTRAINT [PK_tblTSEmpRules] PRIMARY KEY CLUSTERED
(
[fldEmpRuleID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSRule] WITH NOCHECK ADD
CONSTRAINT [PK_tblTimesheetRules] PRIMARY KEY CLUSTERED
(
[fldRuleID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSEmpRules] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSEmpRules_fldLowerBound] DEFAULT (getdate()) FOR
[fldLowerBound],
CONSTRAINT [DF_tblTSEmpRules_fldUpperBound] DEFAULT (getdate()) FOR
[fldUpperBound]
GO

ALTER TABLE [dbo].[tblTSRule] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSRule_fldRosteredStart] DEFAULT ('00:00') FOR
[fldRosteredStart],
CONSTRAINT [DF_tblTSRule_fldMaxMins] DEFAULT (0) FOR [fldMaxMins],
CONSTRAINT [IX_tblTSRules_1] UNIQUE NONCLUSTERED
(
[fldCode],
[fldSubAreaCode]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_tblTSEmpRules] ON
[dbo].[tblTSEmpRules]([fldEmployeeID], [fldDate]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO

CREATE INDEX [IX_tblTSEmpRules_1] ON [dbo].[tblTSEmpRules]([fldDate])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblTSRules] ON [dbo].[tblTSRule]([fldCode]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSEmpRules] ADD
CONSTRAINT [FK_tblTSEmpRules_tblEmployee1] FOREIGN KEY
(
[fldEmployeeID]
) REFERENCES [dbo].[tblEmployee] (
[fldEmployeeID]
),
CONSTRAINT [FK_tblTSEmpRules_tblTSRules] FOREIGN KEY
(
[fldRuleID]
) REFERENCES [dbo].[tblTSRule] (
[fldRuleID]
) NOT FOR REPLICATION
GO

ALTER TABLE [dbo].[tblTSRule] ADD
CONSTRAINT [FK_tblTSRules_tblSubArea] FOREIGN KEY
(
[fldSubAreaCode]
) REFERENCES [dbo].[tblSubArea] (
[fldSubAreaCode]
) NOT FOR REPLICATION
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER CreateEmpCaptureBound ON dbo.tblTSEmpRules

FOR INSERT,UPDATE

AS

-- Calculate the EARLIEST POSSIBLE clock in time for this
employee/rule/date record and write it to fldLowerBound.
-- Calculate the LATEST POSSIBLE clock out time for this employee/rule
record/date and write it to fldUpperBound.

-- Write data to ALL affected rows.

update tblTSEmpRules

set
fldLowerBound =
dateadd(
mi,
( tblTSRule.fldMaxMins - 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
inserted.fldDate, -- Date on which rule is to be applied
tblTSRule.fldRosteredStart)-- Time
),

fldUpperBound =
dateadd(mi,
( tblTSRule.fldMaxMins + 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
inserted.fldDate,-- Date on which rule is to be applied
tblTSRule.fldRosteredStart)-- Time
)

FROM inserted INNER JOIN
tblTSRule ON inserted.fldRuleID =
tblTSRule.fldRuleID

WHERE inserted.fldEmpRuleID=tblTSEmpRules.fldEmpRuleID

print 'Trigger for TSEmpRules fired...'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER UpdateCaptureBound ON dbo.tblTSRule

FOR UPDATE

AS

update tblTSEmpRules

set
fldLowerBound =
dateadd(
mi,
( inserted.fldMaxMins - 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
tblTSEmpRules.fldDate, -- Date on which rule is to be applied
inserted.fldRosteredStart)-- Time
),

fldUpperBound =
dateadd(mi,
( inserted.fldMaxMins + 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
tblTSEmpRules.fldDate,-- Date on which rule is to be applied
inserted.fldRosteredStart)-- Time
)

FROM inserted

WHERE inserted.fldRuleID = tblTSEmpRules.fldRuleID

print 'update trigger for TSRule fired.....'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO(teddysnips@.hotmail.com) writes:
> What we are finding is that if an update is made on the tblTSRule
> table, it fires TWO triggers - first the UPDATE trigger on the
> tblTSRule table (which updates the tblTSEmpRules table) and then the
> UPDATE trigger on the tblTSEmpRules table. The two triggers are
> virtually identical, but what can we do in terms of design to get
> around this? I should point out that the ability exists in the
> application to amend both rules and the assignment of rules to
> employees.

There are a couple of options.

1) Set the configuration option "nested triggers" to 0. But this is a
server-wide option. I recommend that you leave it on.

2) In the trigger tblTSEmpRules add:

IF NOT EXISTS (SELECT * FROM inserted WHERE
fldLowerBound IS NULL OR fldUpperBound IS NULL)
RETURN

3) In the trigger TSRules add:

CREATE TABLE #no$cascade(a int NOT NULL)

And in the other trigger add:

IF object_id('tempdb..#no$cascade') IS NOT NULL
RETURN

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I have an application to capture and process timesheet information. Put simply, employees clock in and out at various locations. Business logic determines how to process these events... <<

Take a look at the job clock at http://www.exaktime.com. I designed
their database, and I did not need triggers.

I am writing questions for two exams do not have time to go thru your
code right (and clean out all the "tbl-" and 'fld-" prefixes, bring it
up to ISO-11179, remvoe IDENTITY columns, etc.) But based on a quick
scan, it looks like it could made much easier.

Certificate not recognized by SQL 2005 - Encryption Problem

Hi,

We recently upgraded from SQL 2K to SQL 2005 on WINDOWS 2003 Server. The entire upgrade process has run pretty smoothly aside from encryption.

We have ASP.NET 1.x apps that connect to an internal server but fail when encrypted. We have tried all of the steps available on the MS KB and still have not resolved the issue.

On http://msdn2.microsoft.com/en-us/library/ms191192.aspx, we find the following:
To configure the server to accept encrypted connections

1. In SQL Server Configuration Manager, expand SQL Server 2005 Network Configuration, right-click Protocols for <server instance>, and then selectProperties.
2. In the Protocols for<instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.
3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
4. Restart the SQL Server service.

The problem is when we get to step 2, there are NO certificates available in the dropdown box.

This problem is solely the result of installing SQL Server 2005. The apps were successfully encrypting data connections prior to the install. All internal and non-encrypted connections work correctly and no other changes were made to the box aside from the SQL 2005 upgrade.

This issue has remained unresolved for almost two weeks now.... We have tried the KB articles many times over, none of which have made a difference. We have also created certificates many times over, checking Server Authentication in the EKU section and making sure the cert has a private key. The expiration date on the certs we create are in 2009 and are named using the FQDN. Clearly, I'm no certificate expert, but there has got to be something we're missing with the certificates since every other app connection works like it should

What can I do to get certificates to appear in the Certificate tab of the Properties dialog box? What else would you recommend?

Many thanks.

-Matt

Orcabbelle,

To help you troubleshoot this issue, I need you to share some more info,

(1) ERRORLOG of the SQL Server,

You should be able to find it under place similar to

'C:\Program Files\Microsoft SQL Server\MSSQL.XX\MSSQL\LOG\ERRORLOG''

Extract specific lines that show certificate if any.

(2) Run certutil.exe as

certutil.exe -v -store my.

pasted the output w.r.t the cert that you have just installed here.

Generally, there is also way to modify the registry value to force server encryption. But it is not recommended because it is errorprone. Let me know if you want to do that to get you problem solved faster.

|||Nan,

There is only one relevant line I can find in the errorlog:
2006-04-04 11:11:03.59 Server A self-generated certificate was successfully loaded for encryption.

Not sure what this means or where I can find the self-generated cert. Is it the cert in with the data files?

As for #2, we ran the cmd line you suggested and everything looks good aside from:
CERT_ARCHIVED_PROP_ID(19):
3988ce37fa66df4b79d75bb63c7f8d0d_c381bd65-7971-4be2-9c1b-aef143aed909
ERROR: Certificate public key does NOT match stored keyset

|||

So the certificate you are using is corrupted somehow.

One possible reason is that you have a previous installed certificate, and the newly installed certificate does not have permission to over write the private key of the old one. I am assuming you do have a certificates that have both public key and private key is expotable.

Try to clean up your certificate store using MMC. that have same FQDN as this new one has. Then install the certificate in the local store. Try to run as local admin to clean/install the certificate.

Let me know what you get.

Selfsigned certificate is newly added feature in SS2k5 that help to encrypt even user does not have certificate. the self signed certificate is stored in master database. It is briefly mentioned here. As result, it is possible to force encryption without provisioning a valid certificate in SS2k5

http://blogs.msdn.com/sql_protocols/archive/2005/10/04/476705.aspx

|||We cleaned up our MMC and our new cert seems to be fine (not corrupted). We want to use our cert, not the self-signed cert.

As soon as we upgraded, all of our ASP.NET apps that were using Encrypt=True in their connection strings simply stopped working. Nothing else on either box, the server or the client, was changed. We changed the connection string to Encrypt=False and the apps work like they should, aside from not being encrypted. What could cause this?

We have tried just about every MS KB article and recommendation we can find. We created new certificates using a CA server. We made sure they have had private keys and allowed server authentication. Force protocol encryption is set to YES on the server.

We have never been able to get the certificate we create to appear in the list. We are logging on the server as the domain admin and starting the service under the same account.

Any ideas why we can't see the cert in the list or why the encryption failed after a SQL upgrade?

Many thanks!
|||

First, Your sql service should run under admin account, otherwise it has no permissing to the private key and thus the cert would not show up in the drop down list of SSCM, visit KB article for more detail:
http://support.microsoft.com/?kbid=900495.

Secondly, SQL Server 2005 has a new kind cert called self-signed certificate, which means when server can not find a good cert to load in the cert store, it will load such a self-signed cert. the encryption failure you saw is due to the reason that your server might load a self-signed cert(To verify this, you can go to server errorlog and see there is keyword "self-signed certificate"), and you forced client encryption through setting "Encrypt=True". If you already forced server encryption, the connection should be encrypted. And if you require forcing client encryption, you need to have server loading a certificate issued by a trusted CA.

Thirdly, please run "certutil.exe -v -store my", the tool would verify whether the certs you installed are valid;and try change sql service running under LocalSystem account, see whether any cert appear in the list.

|||

The only thing that I can think of is that the certificates that you are generating and the one you used for SS2K do not meet the stricter requirements of SS2K5. Please have a look at this post to see what properties we require from a cert:
http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx.

Thanks,
Il-Sung.

|||II-Sung, thanks again for your help.

Ok, IT has had a look at what you said and has had trouble with #4 found on the link you provided.

4) The Certficate’s key usage must include AT_KEYEXCHANGE property, which means Key Encipherment (a0)

They were able to find the Key Encipherment value but ours is not (a0), it's (f0). Not sure if this makes a difference or not.

From running "certutil.exe -v -store my":

Key Usage

Digital Signature, Non-Repudiation, Key Encipherment, Data Encipherment (f0)

|||

Hi Orcabelle,

Would you be able to try using a certificate that has a key usage of 0xA0 instead of 0xF0 and see if that makes a difference?

Also, could you indicate whether you're generating your own self-signed certificate? If so, what is the command that you're using the generate it. I believe it should be similar to:

makecert -r -pe -n "CN= MySQLServerName" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

Thanks,
Il-Sung.

|||At this point we're happy to try anything....

Now my question is, how do I change key usage from F to A?
|||

Hi Orcabelle,

I did some more looking into this and a key usage of f0 is perfectly fine. This is very perplexing problem. Could you please confirm that you're looking at the correct entry of the "certutil.exe -v -store my" output? i.e., you've properly correlated the cert hash with the one in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate registry key?

Thanks,
Il-Sung.

|||II-Sung,

We have the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate, but it has no value. We also have the cert hash data, but what syntax should the key be in?

If the cert hash is supposed to automatically appear in this registry key, it does not. When the key value is changed to match the thumbprint (cert hash (sha1)) of the certificate then the MSSQL service will not start. When the value is blanked the service starts as it should.
|||Here is the error we recieve from asp.net 2.0 when Encrypt=True...

"System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Security.GetSystemData() in C:\Inetpub\screen\App_Code\Security.vb:line 109
at Security.SecurityCheck() in C:\Inetpub\screen\App_Code\Security.vb:line 94"
|||

Orcabelle,

(1) To configure the server hash in sqlserver 2005, you need to put the ascii form of the Thumbprint in to the registry mentioned above, without any spacec in between and before and after, for example "06065fbcc0fd1b45b04dd67fd236673a9aa6563c". If the certificate specified in the hash is not a valid one from the point a view of sql server, sql server will fail to started. If your case if later, you can look the errorlog, look for certificate related and find what is the root cause.

(2) If you force encryption in the client, asp2.0 in your case, and that the server happens to use a self-signed certificate or a certificate that is not trusted by the client, you will see the error "The certificate chain was issued by an authority that is not trusted". In such case, in order to still use encryption, you can add a connection string property,

"TrustServerCertificate = true", such that client does not validate certificate hierarchy.

http://msdn2.microsoft.com/en-US/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx

SQL book online, "Using Encryption Without Validation" in SQL Server 2005 Books Online." also have related info.

|||So after speaking with Microsoft, we have determined the issue, but we're still awaiting a possible resolution.

The problem we've run into is caused by our using both an internal (abc.local) and external (xyz.com) domain. All this time, we had been using the FQDN for our external domain, but this wasn't being recognized by SQL Server 2005. As soon as we created a certificate using the FQDN for our internal domain, it was recognized.

That said, we still don't know for sure whether client based encryption is possible using SQL Server 2005 with valid but different named domains. We have tested and server based encryption does seem to work, but we still want to know why client based does not. It should be a two way street or so they say.

Anyway, thank you all for the help you provided. I will post Microsoft's response/recommendations once we hear from them.

Have a good day.

-Matt

Cast/Convert value to VB variable

Hi all,
I have a VB 6.0 application that interacts with an MS Access backend. I am in the process of converting it so that it interacts with SQL Server 2000. The current applications uses Cint, CStr, etc. functions which are MS-Access specific, so I am now switching over to Convert (or Cast) function. The problem is that there are certain places wherein I first need to store the value of the resultant CAST/Convert function in a variable and then use that value in a SQL statement. However, I cant seem to figure out a way of storing the results from CAST/CONVERT functions in the VB 6.0 variable. All the examples on Internet show use of these functions directly in an SQL statement e.g. "Select CAST(title as Int) from xyz", etc.
Can anybody tell me how can i get the values to be stored in the variable? I am really stuck here and cant seem to progress.

Thanks in advance for all your help.

Regards:
Prathmeshe.g.

set rs=cmd.execute("select cast(title as int) as title from xyz")

title=rs.fields("title")|||Thanks for the reply oj. However, my requirement is somewhat different. I'll explain the scenario in short. The user chooses a filename to delete, which is stored in the database as a record. It is stored in 2 places in the database. One as a whole filename and in the second place as a breakdown record. The program should delete the filename from the database and also the file from the disk location. The filename in the database is stored as say "XY006CV003A.xls" or "XY005CJ003B.doc" however on the disk they are stored with the above number and the title for the document, concatenated e.g "XY006CV003A test.xls". So I need to extract the file name only which is "XY006CV003A.xls" to match the database record. The last part "003" is sort of a sequence number and is stored in the database. I need to extract the that sequence to match it and delete it from the second place as I have mentioned. '003' when extracted from the filename will be a string and I need to cast it to Integer type to match the record.

e.g.
fname = Split("XY006CV003A.xls",".") gives "XY006CV003A"
seq = Mid(fname, 8, 3) gives '003' which is string format

I now need to use this seq variable in the query

"Select * from XYZ where fileseq=" & seq

fileseq is of integer datatype so I need to cast/convert seq variable to Integer from String.
Can anybody suggest any ideas?

Regards:
Prathmesh