Chaging SSN Format

This is a repost due to know response:
If I use this formula:
SELECT LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
as SSN, Clients.First_Name + ' ' + Clients.Last_Name
FROM Clients
It recodes the SSN as I need it. How do I get this result to the UPDATE
function inside the main db?
Possibly:
Update Clients
Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
I tried that code but get a syntax error. Any ideas?> Possibly:
> Update Clients
> Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
> 4)
Try:
UPDATE dbo.Clients
SET ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
RIGHT(ssnum, 4)
However, I generally suggest storing unformatted data and instead format
strings in the application/report during presentation
Hope this helps.
Dan Guzman
SQL Server MVP
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:E9C89F84-1950-4670-A852-49033092BD83@.microsoft.com...
> This is a repost due to know response:
> If I use this formula:
> SELECT LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
> 4)
> as SSN, Clients.First_Name + ' ' + Clients.Last_Name
> FROM Clients
> It recodes the SSN as I need it. How do I get this result to the UPDATE
> function inside the main db?
> Possibly:
> Update Clients
> Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
> 4)
> I tried that code but get a syntax error. Any ideas?

chack attribute domain

How can I be sure that a attribute in a table only can accepta {1,2,3}

You could add CHECK constraint

Code Snippet

createtable test

(

id int,

valueint

)

altertable test addconstraint chk_possible check(valuein(1,2,3))

insertinto test values(1,1)--Works

insertinto test values(1,7)--Fail

Chaching Sqldatasource datasets... can it be done? and a performance question.

I'm using visual basic coded datasets right now, and caching them, so as all of my web app users access the cache once the first person's gone to my site and initiallized the page.

If Cache("ds_cache")IsNothingThenDim dsAsNew DataSet

blah blah code blah blah blah

Cache.Insert("ds_cache", ds)EndIf

What I was wondering is, if it's possible to cache the datasets that get created and are used by Sqldatasources. Or if by chance caching is automatically done and controlled for these datasets, as it's the server that makes and handles them.

The reason I wonder about this because I'm not sure how you go about referencing one of those datasets, because you can't give them a name/id. And I've not found mention anywhere of you being able to do so.

The real point or question I'm trying to get at is to see which performs better , or uses less requests back to the server for the data as a lot of my data will be repeatedly used by all of my users and so I'd like to save myself from using as many repeated connections as possibles in regards to using a typed dataset versus filling my controls from a sqldatasource?

Thank you in advance for your response.

You know what, I pretty much found my answer:

http://msdn2.microsoft.com/en-us/library/z56y8ksb.aspx

The question still remains of if a sqldatasource would outperform a typed dataset?

It makes you wonder, as a typed dataset you don't have to worry about time spent on conversions, etc. but you would think that server built and controlled dataset might perform better, or atleast it would probably build faster when it's first created... please do reply if you have insight.

Chaching Sqldatasource datasets... can it be done? and a performance question.

I'm using visual basic coded datasets right now, and caching them, so as all of my web app users access the cache once the first person's gone to my site and initiallized the page.

If Cache("ds_cache") Is Nothing Then

Dim ds As New DataSet

blah blah code blah blah blah

Cache.Insert("ds_cache", ds)

End If

What I was wondering is, if it's possible to cache the datasets that get created and are used by Sqldatasources. Or if by chance caching is automatically done and controlled for these datasets, as it's the server that makes and handles them.

The reason I wonder about this because I'm not sure how you go about referencing one of those datasets, because you can't give them a name/id. And I've not found mention anywhere of you being able to do so.

The real point or question I'm trying to get at is to see which performs better , or uses less requests back to the server for the data as a lot of my data will be repeatedly used by all of my users and so I'd like to save myself from using as many repeated connections as possibles in regards to using a typed dataset versus filling my controls from a sqldatasource?

Thank you in advance for your response.

You know what, I pretty much found my answer:

http://msdn2.microsoft.com/en-us/library/z56y8ksb.aspx

The question still remains of if a sqldatasource would outperform a typed dataset?

It makes you wonder, as a typed dataset you don't have to worry about time spent on conversions, etc. but you would think that server built and controlled dataset might perform better, or atleast it would probably build faster when it's first created... please do reply if you have insight.

CFE2SQL Server does not exist or access denied.

Does anyone have any idea what is causing the following error.
"CFE2SQL Server does not exist or access denied."
--
Thanks
JDSJDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.|||The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:
> JDS wrote:
> > Does anyone have any idea what is causing the following error.
> >
> > "CFE2SQL Server does not exist or access denied."
> >
> >
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offer.
>|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.

CFE2SQL Server does not exist or access denied.

JDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:

> JDS wrote:
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offe
r.
>|||Does anyone have any idea what is causing the following error.
"CFE2SQL Server does not exist or access denied."
Thanks
JDS|||JDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.|||The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:

> JDS wrote:
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offe
r.
>|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.

CF / SQL Server syntax error HELP

I am not sure why this is producing a SQL Server related error, but w/o
having an instance of SQL Server on my machine to verify anything further,
can you all help me with this?

<!-- validate() -->
<cffunction name="validate" access="remote" returnType="numeric">
<cfargument name="username" required="yes" type="string" />
<cfargument name="password" required="yes" type="string" /
<cfquery name="validate" datasource="#request.dsn#">
SELECT userID
FROM User
WHERE username = '#UCase(username)#'
AND password = '#UCase(password)#'
</cfquery
<cfif validate.RecordCount EQ 0>
<cfquery name="log" datasource="#request.dsn#">
UPDATE User
SET lastLoggedIn = #createOdbcDate(now())#
WHERE userId = #validate.userID#
</cfquery>
<cfreturn validate.userID />
<cfelse>
<cfreturn 0 />
</cfif>
</cffunction
Sorry that's all I can honestly provide, other than the error being on the
line with <cfif validate.RecordCount
PhilOn Tue, 14 Oct 2003 20:37:57 -0400, "Phil Powell" <soazine@.erols.com>
wrote:

>I am not sure why this is producing a SQL Server related error, but w/o
>having an instance of SQL Server on my machine to verify anything further,
>can you all help me with this?
><!-- validate() -->
> <cffunction name="validate" access="remote" returnType="numeric">
> <cfargument name="username" required="yes" type="string" />
> <cfargument name="password" required="yes" type="string" />
> <cfquery name="validate" datasource="#request.dsn#">
> SELECT userID
> FROM User
> WHERE username = '#UCase(username)#'
> AND password = '#UCase(password)#'
> </cfquery
OK you want to select the userid that has a username of #username# and
a password of #password#.

> <cfif validate.RecordCount EQ 0
Now if no user is found, update the users last login date. This code
will throw an error because validate.userID will not be defined.
Because you are trying to update a user only when no user is found.
It should not throw a database error but rather validate.userid is not
defined and that will throw an error. replace the above if statement
with the one below, and if a problem still remains please provide the
error messages given.

<cfif validate.recordCount GT 0
and now every thing should work fine.

> <cfquery name="log" datasource="#request.dsn#">
> UPDATE User
> SET lastLoggedIn = #createOdbcDate(now())#
> WHERE userId = #validate.userID#
> </cfquery>
> <cfreturn validate.userID />
> <cfelse>
> <cfreturn 0 />
> </cfif>
> </cffunction>
>Sorry that's all I can honestly provide, other than the error being on the
>line with <cfif validate.RecordCount>
>Phil

Also you can download a 120 trial of MS SQL server and if you are
running windows 2000+ you can run it.

Charliek|||> Now if no user is found, update the users last login date. This code
> will throw an error because validate.userID will not be defined.

Slight correction...Even if the query returns 0 resutlts validate.userID
will be defined it will just be blank.
The error from SQL is probably saying "Incorrect syntax near '='."
The cause of the problem is still the same...your if statement should be GT
or NEQ 0.
Side note bad form calling a table user...that is an SQL reserve word I
thought...

Adam H

"charliek" <charlie_knudsen@.hotmail.com> wrote in message
news:g4hpov4h8uauvqr91q1par5fu4g5fqeeto@.4ax.com...
> On Tue, 14 Oct 2003 20:37:57 -0400, "Phil Powell" <soazine@.erols.com>
> wrote:
> >I am not sure why this is producing a SQL Server related error, but w/o
> >having an instance of SQL Server on my machine to verify anything
further,
> >can you all help me with this?
> ><!-- validate() -->
> > <cffunction name="validate" access="remote" returnType="numeric">
> > <cfargument name="username" required="yes" type="string" />
> > <cfargument name="password" required="yes" type="string" />
> > <cfquery name="validate" datasource="#request.dsn#">
> > SELECT userID
> > FROM User
> > WHERE username = '#UCase(username)#'
> > AND password = '#UCase(password)#'
> > </cfquery>
> OK you want to select the userid that has a username of #username# and
> a password of #password#.
> > <cfif validate.RecordCount EQ 0>
> Now if no user is found, update the users last login date. This code
> will throw an error because validate.userID will not be defined.
> Because you are trying to update a user only when no user is found.
> It should not throw a database error but rather validate.userid is not
> defined and that will throw an error. replace the above if statement
> with the one below, and if a problem still remains please provide the
> error messages given.
> <cfif validate.recordCount GT 0>
> and now every thing should work fine.
> > <cfquery name="log" datasource="#request.dsn#">
> > UPDATE User
> > SET lastLoggedIn = #createOdbcDate(now())#
> > WHERE userId = #validate.userID#
> > </cfquery>
> > <cfreturn validate.userID />
> > <cfelse>
> > <cfreturn 0 />
> > </cfif>
> > </cffunction>
> >Sorry that's all I can honestly provide, other than the error being on
the
> >line with <cfif validate.RecordCount>
> >Phil
> Also you can download a 120 trial of MS SQL server and if you are
> running windows 2000+ you can run it.
> Charliek

CeWriteRecordProps failling with a empty FILETIME field..

Hi All, Does anyone have already made an application that writes some data into the EDB Pocket database ? I′m trying to insert a record with a empty FILETIME field and I′m getting the Error 87 (INVALID_PARAMETER), I′ve tested with some date in the field and the record is added sucessfull, it seems that the database doesn′t accept anymore empty FILETIME fields? Is it true? I didn't find anything in the docs.

thanx in advance.

The CEPROPVAL is OEM dependent. Have you verified the behavior on other devices too? If that remains the same, I need to investigate further.|||

Hi Thiago,

Are you using CeWriteRecordProps (EDB)? If so, then yes you would get error if you are passing empty FILETIME field. Somehow, this is missed in documentation. We have a bug for that, and I would check the status.

Please pass non-empty FILETIME to get yourself unblocked. Sorry for the pain created.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

|||

Hi Laxmi,

Thank you very much for your reply, I′m passing now a non-empty FILETIME and it's working very well, now I'm stuck in another function, a similar problem with the CeSeekDatabaseEx funcion, especially when used with the CEOID, BEGINNING, LAST or CURRENT flags.

In the documentation, MSDN says that isn't necessary to open the database with a sort order when seeking with these flags, but if I do this, get the same ERROR 87 (INVALID_PARAMETER).

The function only works (find a record sucessfully) when I open the database specifing a sort order.

Do you know something about this issue?

In the documentation is:

The ERROR_INVALID_PARAMETER may be returned in the following situations:

x. The database was opened without a specified sort order, and dwSeekType is
a value other than the following:
CEDB_SEEK_CEOID
CEDB_SEEK_BEGINNING
CEDB_SEEK_CURRENT
CEDB_SEEK_END.

All other seek types require the use of a sort order.

link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wcedata5/html/wce50lrfCeSeekDatabaseExEDB.asp

Thanx in advance,
Thiago

CeWriteRecordProps failling with a empty FILETIME field..

Hi All, Does anyone have already made an application that writes some data into the EDB Pocket database ? I′m trying to insert a record with a empty FILETIME field and I′m getting the Error 87 (INVALID_PARAMETER), I′ve tested with some date in the field and the record is added sucessfull, it seems that the database doesn′t accept anymore empty FILETIME fields? Is it true? I didn't find anything in the docs.

thanx in advance.

The CEPROPVAL is OEM dependent. Have you verified the behavior on other devices too? If that remains the same, I need to investigate further.|||

Hi Thiago,

Are you using CeWriteRecordProps (EDB)? If so, then yes you would get error if you are passing empty FILETIME field. Somehow, this is missed in documentation. We have a bug for that, and I would check the status.

Please pass non-empty FILETIME to get yourself unblocked. Sorry for the pain created.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

|||

Hi Laxmi,

Thank you very much for your reply, I′m passing now a non-empty FILETIME and it's working very well, now I'm stuck in another function, a similar problem with the CeSeekDatabaseEx funcion, especially when used with the CEOID, BEGINNING, LAST or CURRENT flags.

In the documentation, MSDN says that isn't necessary to open the database with a sort order when seeking with these flags, but if I do this, get the same ERROR 87 (INVALID_PARAMETER).

The function only works (find a record sucessfully) when I open the database specifing a sort order.

Do you know something about this issue?

In the documentation is:

The ERROR_INVALID_PARAMETER may be returned in the following situations:

x. The database was opened without a specified sort order, and dwSeekType is
a value other than the following:
CEDB_SEEK_CEOID
CEDB_SEEK_BEGINNING
CEDB_SEEK_CURRENT
CEDB_SEEK_END.

All other seek types require the use of a sort order.

link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wcedata5/html/wce50lrfCeSeekDatabaseExEDB.asp

Thanx in advance,
Thiago

Certification: Looking for advise

Hi All

I have been working with Ms access Database the last four years and have gained a lot of experience which help me to have extensive knowledge of Jet, Dao and Ado. Since The Begining of this year i hv been migrating to SQL server server and for that i bought two books :Programming Access project file with Microsoft Sql server and Access Developers Guide to Ms sql server : Chipman & Baron ( this oner is simply a great achievement ). They help me out in the very near past to complety migrate a two database approch Jet Application to a reliable (Adp/ SQL Server client server ) where major parts of the code business logic has been transfered to run on the server as stored procedures and all forms re-built as unbound. these merely leads to a great enhancement of the applications.

My goal now is register to MCDBA and get the certification. I am not interested by classroom training from Microsoft Parterns or online training. I am looking for books that can help to get well prepared for the exams.

Have found this two books in Amazon:

1 - MCAD/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229, Second Edition

2- MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 System Administration, 70-228, Second Edition

However their rating didn't encourage me to place directly my orders.

Does someone have any experience with them ?

Do you think they will considerably help me to add value to my knowledge of SQL Server and get ready for the exam ?

Do you know some others books you woul rather encourage me to buy ?

Any advice in this regards really appreciated.Transcender

www.transcender.com

Regards,

hmscott|||The MS Press books are not the best for learning. Try the Que or Sybex books for learning the material while preparing for certification. Make sure you get hands-on experience with all the examples and labs as you read the books. Use Transcender after reading the books to assess your readiness to take the test. Good luck!

Certification training?

I hope this is the right place to ask this. Does anyone know places
that offer SQL Server 2005 certification training and testing (MCTS,
MCITP) in South Florida (Palm Beach County and surrounding area)?

I've tried searching around on the web, and it's hard to find what I'm
looking for mixed in with all the junk. I found a couple of places that
have the training I want, but I'm sure I didn't find all of them. So
far, I'm not overly impressed, so more options would be a good thing.

Also, about how much do those types of classes usually cost? How about
the tests? I might just go the self study route, instead of a class,
but I still need to know how much to expect to pay for testing.

Thanks in advance,

--Richardblueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:

Quote:

Originally Posted by

I hope this is the right place to ask this. Does anyone know places
that offer SQL Server 2005 certification training and testing (MCTS,
MCITP) in South Florida (Palm Beach County and surrounding area)?
>
I've tried searching around on the web, and it's hard to find what I'm
looking for mixed in with all the junk. I found a couple of places that
have the training I want, but I'm sure I didn't find all of them. So
far, I'm not overly impressed, so more options would be a good thing.
>
Also, about how much do those types of classes usually cost? How about
the tests? I might just go the self study route, instead of a class,
but I still need to know how much to expect to pay for testing.

I entered "SQL Server 2005 certification training" and "Palm Beach" in
the search field at Google, and ended up at
http://www.mcse-training-classes.co...lm-Beach-FL.htm
--
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

Certification Signature

I already have MCP status. Assuming I earn MCTS credential, what is the
appropriate signature?
1) John Doe, MCTS, MCP
Or
2) John Doe, MCTS
While reading user posts, I can't seem to find a consistant useage regarding
signatures.
My question is general in nature, as I plan on also getting MCSA/MCSE.
Thanks!please just put one...whichever you want noticed...:)
None is better.
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"labsRcool@.community.nospan"
<labsRcoolcommunitynospan@.discussions.microsoft.com> wrote in message
news:750ACE96-469F-4023-9D4B-8125698DB310@.microsoft.com...
>I already have MCP status. Assuming I earn MCTS credential, what is the
> appropriate signature?
> 1) John Doe, MCTS, MCP
> Or
> 2) John Doe, MCTS
> While reading user posts, I can't seem to find a consistant useage
> regarding
> signatures.
> My question is general in nature, as I plan on also getting MCSA/MCSE.
> Thanks!

certification question

I am reading Administering SQL Server, a study guide for 70-228
on page 27, it says:
"INSTEAD OF triggers are useful when a DML operation is unsuccessful."
I think this is nonsense. Any comments?Ford Desperado (ford_desperado@.yahoo.com) writes:
> I am reading Administering SQL Server, a study guide for 70-228
> on page 27, it says:
> "INSTEAD OF triggers are useful when a DML operation is unsuccessful."
> I think this is nonsense. Any comments?

Without any context at all, the sentence certainly looks funny. It reminds
of what I once read in a manual from DEC: "Frequently, unexpected errors are
entirely unpredictable."

I guess what they are trying to say is that when you cannot insert, update
or delete directly on a view, because the view does not live up to the
rules, then you can achieve this with an INSTEAD OF trigger.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>Without any context at all, the sentence certainly looks funny.

well there isn't any more context.
Thanks Erland|||It's certainly a strange comment in the absence of any context, as
Erland says. Another possible explanation might be that if you have to
INSERT data into a table where you know that some data will violate
constraints, an INSTEAD OF trigger could be used to INSERT only the
good data, and flag or log the bad data; if you did a single INSERT,
then it would all be rolled back.

Simon|||One more nonsense:

the book claims that db_denydatawriter " Can deny the write permissions
on any object"

According to BOL, db_denydatawriter Cannot modify any data in any user
table in the database, and BOL seems to be correct

I think the author, Joyjit Mukherjee, screwed it up|||It sounds like he took a guess based on the group name, rather than
look it up in BOL, which would make me wonder about the quality of the
rest of the book. I've seen the official MS Press MCDBA study guides,
and they seem to be quite good, but then I suppose they should be.

Incidentally, sp_dbfixedrolepermission gives quite a detailed (and
accurate) list of each role's permissions - it's a bit clearer than the
comments in BOL.

Simon

Certification Question

Hi all,

I am new to Sql Server 2005

I planning to take exam MCTP exam 70-445

is the cert 70-431 a pre-requisite for certs 70-445 and 70-446?

Thanks and regards

Currently, according to the 'official' Microsoft websites, 431 is NOT required for SQL BI certification.

See:

http://www.microsoft.com/learning/mcp/mcts/bi/default.mspx
http://www.microsoft.com/learning/mcp/mcitp/bid/default.mspx

Certification Exam on reporting service

Does anybody have any idea if there is certification exam yet?
--
SincerelyThere is course 2030
(http://www.microsoft.com/traincert/syllabi/2030AFINAL.ASP) but it is not
part of any certification track yet.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:AD66A6C8-22D8-4388-86BE-3060C84013F1@.microsoft.com...
> Does anybody have any idea if there is certification exam yet?
> --
> Sincerely
>

Certification

Hi All,
This post isn't particularly directly related to the Group but I
thought I would get some opinions.
I use SQL Server 2000 on a daily basis - both administering &
programming, coupled with VB6 client applications. I am currently
learning VB .NET
I am thinking about getting some kind of certification as I learn VB
.NET. I have been looking at the MCAD track on the Microsoft site. At
my current programming level this seems to be a good starting point. I
passed Exam 70-229 last year, which counts as an elective exam towards
the MCAD certification.
Does anyone have any advice/opinions for my current situation.
I am aware that there is the MCSD certification, however, I was
planning to tackle that one a bit later - see how I get on with the
MCAD first?
Thanks in advance for any comments.
BarryMicrosoft is in the process of rolling out new exams and certifications:
http://www.microsoft.com/learning/mcp/newgen/
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1139511623.548378.271810@.g44g2000cwa.googlegroups.com...
> Hi All,
> This post isn't particularly directly related to the Group but I
> thought I would get some opinions.
> I use SQL Server 2000 on a daily basis - both administering &
> programming, coupled with VB6 client applications. I am currently
> learning VB .NET
> I am thinking about getting some kind of certification as I learn VB
> .NET. I have been looking at the MCAD track on the Microsoft site. At
> my current programming level this seems to be a good starting point. I
> passed Exam 70-229 last year, which counts as an elective exam towards
> the MCAD certification.
> Does anyone have any advice/opinions for my current situation.
> I am aware that there is the MCSD certification, however, I was
> planning to tackle that one a bit later - see how I get on with the
> MCAD first?
> Thanks in advance for any comments.
> Barry
>

Certification

Can anyone guide me on how to prepare for exam 229. There is just ton of
information out there. What is a good begining point? I'll appreciate if
someone can send me a link for good mock exams.Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen Delaney.
Read (and understand) it cover-to-cover and you'll be all set.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
> Can anyone guide me on how to prepare for exam 229. There is just ton of
> information out there. What is a good begining point? I'll appreciate if
> someone can send me a link for good mock exams.
>|||ACK
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> schrieb im
Newsbeitrag news:eHnn$tccFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen
> Delaney.
> Read (and understand) it cover-to-cover and you'll be all set.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
>|||agreed.
also try to get the Transcender Exams.
Greg Jackson
PDX, Oregon

Certification

Can anyone guide me on how to prepare for exam 229. There is just ton of
information out there. What is a good begining point? I'll appreciate if
someone can send me a link for good mock exams.
Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen Delaney.
Read (and understand) it cover-to-cover and you'll be all set.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"docsql" <docsql@.noemail.nospam> wrote in message
news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
> Can anyone guide me on how to prepare for exam 229. There is just ton of
> information out there. What is a good begining point? I'll appreciate if
> someone can send me a link for good mock exams.
>
|||ACK
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> schrieb im
Newsbeitrag news:eHnn$tccFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen
> Delaney.
> Read (and understand) it cover-to-cover and you'll be all set.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
>
|||agreed.
also try to get the Transcender Exams.
Greg Jackson
PDX, Oregon

Certification

I am just wondering if I wanna upgrade my MCDBA 2000 certification to MCDBA
2005 then which exam I have to clear ?
Thanks
Hi,
google should be your friend allthe time before asking in the groups:
http://www.microsoft.com/learning/mcp/mcitp/dbadmin/default.mspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||That means only one exam (Exam 70-447 to upgrade from 2000 to 2005 ?
Please advice.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1169066687.258676.201810@.a75g2000cwd.googlegr oups.com...
> Hi,
> google should be your friend allthe time before asking in the groups:
> http://www.microsoft.com/learning/mcp/mcitp/dbadmin/default.mspx
>
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
|||Hi,
and 70-431 in addition.
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de

Certification

Can anyone guide me on how to prepare for exam 229. There is just ton of
information out there. What is a good begining point? I'll appreciate if
someone can send me a link for good mock exams.Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen Delaney.
Read (and understand) it cover-to-cover and you'll be all set.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
> Can anyone guide me on how to prepare for exam 229. There is just ton of
> information out there. What is a good begining point? I'll appreciate if
> someone can send me a link for good mock exams.
>|||ACK
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> schrieb im
Newsbeitrag news:eHnn$tccFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen
> Delaney.
> Read (and understand) it cover-to-cover and you'll be all set.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
>> Can anyone guide me on how to prepare for exam 229. There is just ton of
>> information out there. What is a good begining point? I'll appreciate
>> if
>> someone can send me a link for good mock exams.
>>
>|||agreed.
also try to get the Transcender Exams.
Greg Jackson
PDX, Oregon

Certificates versus Keys

Hi,

A few questions:

Are there best practices regarding when a Certificate is appropriate for encrypting data versus using a asymmetric key?

Also, when you create an asymmetric key - aren't you really creating both the public/private key pair?

Lastly, what can a Certificate do that an asymmetric key cannot?

Thanks much,

JoeYes, when you create the asymmetric key, you create a key pair. The same thing happens for certificates if you use the "WITH SUBJECT" clause instead of creating the certificate from a file.

In terms of encryption and signing, what you can do with one, you can also do with the other. The main differences are in how you create them (certificates can be imported from X.509 files, asymmetric keys from assemblies) and the fact that certificates can be backed up to files, while asymmetric keys cannot.

This last point is very important because it means that if an asymmetric key is created internaly, not loaded from a file, then it is not easy (it is possible but not practical) to create the same asymmetric key in another database. So if you do code signing and you want the signature to grant server-level permissions, it will be more convenient to use certificates.

Thanks
Laurentiu|||

That answers my questions Laurentiu. As always, thanks again for your help.

Best Regards,

Joe

Certificates expiration Date

Hi There

This may seem like a stupid question but i am trying to get the hang of the new security model.

I have not really heard anything mentioned about certficate expiration date when it comes to creating certificates for keys or service broker endpoints etc.

We have created certificates for keys and service broker endpoints, now what exactly happens when the expiration date, by default 1 year i think is reached, will we no longer be able to decrypt encrypted data and will the service broker endpoints stop working etc ?

Or is this expiration date when the certificate can no longer be used to create security objects ? And all security objects already created with this certificate will always work ?

In other words is there ever danger that keys and endpoints or basically any object referrencing this certificate will just suddenly stop working one day, or will all objects work indefinately regardless of an certificate/objects expiration date ?

Thanx

The certificate expiration date is not enforced by SQL Server directly. These certificates can still be used for data encrypting, decrypting, signing, verifying signatures or object creation/usage in SQL Server.

Service Broker will honor the expiration date and after a certificate has expired it cannot be used (See “Certificates for Dialog Security” http://msdn2.microsoft.com/en-us/library/ms166117.aspx ).For more information on Service broker usage of certificates I would recommend to visit Service Broker Conversations webpage at http://blogs.msdn.com/remusrusanu/.

While SQL Server itself will not prevent you from using an expired certificate, it is up to the application developers to enforce or suggest the expiration, and use the available usage of expired certificates as a disaster recovery tool rather than as a rule. I strongly recommend generating new certificates to substitute expired ones.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

certificate start date is tomorrow - how to make it today?

Hi,

I use MS certificate server to request/make server certs but the "not before", or start date is tomorrow for a 1 year cert. I dont care how long but I want the cert to start immediately (today).

Regards,

Simon.

If you are importing this certificate into SQL Server for use with encryption and signing, start and expiration are actually ignored by the server so you can use it as soon as you import it.

Otherwise, I would consult the documentation that came with Microsoft Security Server? I will try to do some research and post anything I find.

Thanks,

Sung

|||

Thanks Sung,

It's not actually for SQL server so maybe I missed the mark for the correct forum for this one.

I know lots of apps ignore the cert's validity dates which is maybe why this problem doesn't get reported more often. Also, I am in Australia so maybe their is some UTC effect going on.

Any help would be appreciated.

Simon.

|||

I think you are referring to the following known issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125262. The issue is that the certificate validity dates are stored based on the local time instead of using GMT. The workaround is to explicitly set the start_date to the desired GMT time.

Thanks
Laurentiu

|||

Sorry, this is not a SQL server problem but a CertSrv problem and I dont know how to specify these validity dates for it.

Question reposted on "Security for Applications in Windows Vista "

Thanks,

Simon.

|||

Btw, for anyone looking,

My problem was just the timezone on the server was GMT-8 (USA), not GMT+10 (Australia) so the certs were out by 24 hours.

Cheers.

Certificate security and Dynamic Routing

I've been reseaching on SSB and have read quite a number of posts on this forum that closely relate to what im trying to achieve. I have a solution im designing that ideally consists of a central server (SQLENTERPRISE) that will receive messages asynchronously from remote clients (SQLEXPRESS) spanning a wide geographical region over a GPRS virtual private network on a TCP/IP transport. This ideally is a star and spoke architecture and requirements dictate high level security, no loss of messages whatsover as well as high reliability and scalability.

To meet the security requirement in the context of the above scenario, i was thinking implementing both dialog and endpoint security using certificates would be ideal. I've downloaded some samples and have encountered problems simulating the above scenario on 3 machines (I Server & 2 Clients). Client1 sends messags successfully, the 2nd client doesnt possibly because the certificate on the server matches that to client1 since i ran that script first. How do i make it that the Server shares one certificate with all remote clients? Or is there a better way to configure SSB to work in this scenario?

Secondly i have hard coded the Server's IP Address in the Routes created on the Clients. Considering this is over a GPRS Virtual network, how can i make the clients dyamically "discover" the Server? The idea here is to make adding and setting up of new remote clients easy so that you can just plug them in to the existing network

Any help or pointers would be greatly appreciated.

PapaLee

this is exactly what you need:

http://www.sqlteam.com/article/centralized-asynchronous-auditing-across-instances-and-servers-with-service-broker

Certificate security and Dynamic Routing

I've been reseaching on SSB and have read quite a number of posts on this forum that closely relate to what im trying to achieve. I have a solution im designing that ideally consists of a central server (SQLENTERPRISE) that will receive messages asynchronously from remote clients (SQLEXPRESS) spanning a wide geographical region over a GPRS virtual private network on a TCP/IP transport. This ideally is a star and spoke architecture and requirements dictate high level security, no loss of messages whatsover as well as high reliability and scalability.

To meet the security requirement in the context of the above scenario, i was thinking implementing both dialog and endpoint security using certificates would be ideal. I've downloaded some samples and have encountered problems simulating the above scenario on 3 machines (I Server & 2 Clients). Client1 sends messags successfully, the 2nd client doesnt possibly because the certificate on the server matches that to client1 since i ran that script first. How do i make it that the Server shares one certificate with all remote clients? Or is there a better way to configure SSB to work in this scenario?

Secondly i have hard coded the Server's IP Address in the Routes created on the Clients. Considering this is over a GPRS Virtual network, how can i make the clients dyamically "discover" the Server? The idea here is to make adding and setting up of new remote clients easy so that you can just plug them in to the existing network

Any help or pointers would be greatly appreciated.

PapaLee

this is exactly what you need:

http://www.sqlteam.com/article/centralized-asynchronous-auditing-across-instances-and-servers-with-service-broker

Certificate Restore Not working.

I have been trying to use Encryption to encrypt a few key fields. So my first goal was to create all the items I would need to effectivly use encryption, to encrypt a few key fields.

First I created a sample sql script to create a certificate, create a table and insert some test data encrypted. And then made sure I could decrypt the data encrypted with the certificate.

Second step was to test the backing up of the certificate, droping the certificate to similate a restore of the database. And then a restore of the certificate, and then see if the it was still possible to decrypt the existing encrypted data with the restored certificate.

I receive no errors when restoreing the certificate, but it does not properly decrypt the exisiting data.

Can anyone help and point out my mistake?

--Start Sql--

--Create Sample Cert CREATE CERTIFICATE SampleCert1 ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' WITH SUBJECT = 'Sample Cert for Demo', EXPIRY_DATE = '04/18/2017'; --create Sample table with encrypted version CREATE TABLE Table_1 (Id int NOT NULL IDENTITY (1, 1), SSN_Encrypted varbinary(300) NOT NULL) ON [PRIMARY] GO --insert row with encrypted version. Insert into Table_1 (SSN_Encrypted) values (EncryptByCert(Cert_ID('SampleCert1'),'000-00-0000')) --returns '000-00-0000' for 'SampleCert1' since it was able to decrypt select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 GO --backup Certificate BACKUP CERTIFICATE SampleCert1 TO FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY ( FILE = 'D:\Backups\SampleCert.pvk' , DECRYPTION BY PASSWORD = N'728AC41753642403251BF8E7233EC0C' , ENCRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh' ); GO DROP CERTIFICATE SampleCert1; GO --Restore from backup. to simulate a restore from tape CREATE CERTIFICATE SampleCert1 FROM FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk', DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh'); --This should return the same ssn as was encrypted, if returns null the restore failed. select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 --cleanup Removes Certificate from the system DROP CERTIFICATE SampleCert1; --removes the temporary table. Drop table Table_1

I think I found out why your statement is not working as you expected.

In the following query, the certificate is re-created from the backup, but only a decryption password (used to open the PVK file) is specified, but no encryption password is present. This will create the CERTIFICATE in SQL Server with the private key protected by the DB master key.

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh');

Instead of

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh',

ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C');

You can verify if my assumption is correct by running the following statement:

SELECT name, pvt_key_encryption_type_desc FROM sys.certificates

If I am right, you can just change the protection mechanism for the existing CERTIFICATE:

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' );

Let us know if this information helped.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Well today is not my lucky day I am afraid. I can't verify yet.

Last night we rolled out a policy to enforce passwords complexity, and length requirements.

So I am geting an error just trying to run the same script, even though I am using NT Authentication, and have updated my password to conform to the new policy.

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

Does Create Certificate use a different security creditial then the loged in Users?

I even tried using SA with the following set. with the same results.

Code Snippet

Alter Login [sa] with CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Any more idea's where I need to look to figure this out?

|||

Disabling the password policy checks is not available for cryptographic objects (certificates, keys, etc.), the only part of the policy enforced in such objects is the password complexity for newly created or modified objects/passphrases.

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '<<new password that complies with password policy>>' );

--returns '000-00-0000' for 'SampleCert1' since it was able to decrypt

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted, <<new password that complies with password policy>>') as varchar(12)) as 'SampleCert1'

from Table_1

Or if you prefer to use the DBMK to protect your certificate, you can change your query to the following:

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted) as varchar(12)) as 'SampleCert1'

from Table_1

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you for all your help.

Knowing what password I had to fix made it short work geting it working.

Owen.

CERTIFICATE PROBLEM WHILE ENABLING SSL ON SQL SERVER 2005

Hi ,

How do we install a certificate for enabling security on MS -SQL Server 2005?
I tried to install through MMC but it says no " certificate authority found " . Any
help will be highly appreciated , Thanks to you all in advance :-)

Best Regards,

Sudhansu Tiwari

You should look at this article but are you sure your problem is missing certificate authority. Can you detail the steps you are using one by one.

http://support.microsoft.com/kb/318605

|||

Sudhansu,

Please also have a look at the following blog postings:

http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

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

for additional information about what type of SSL certificate SQL Server supports and how to configure SQL Server to use the SSL certificate.

HTH,

Jimmy

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

Certificate not found

Hello,

I have two different instances of sql server 2005 but i get

Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.

This is one of the two instances:

use master

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'

create master key encryption by password = 'hello'

create certificate [Certificato2]

from file = 'c:\certs\TransportCert2.cer'

with private key (FILE='c:\certs\TransportCert2.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE LOGIN [M02] WITH PASSWORD = 'wrPqYkr%bm3';

ALTER LOGIN [M02] DISABLE;

CREATE USER [M02] FROM LOGIN [M02];

GO

create certificate [Certificato1]

authorization [M02]

from file = 'c:\certs\TransportCert1.cer'

active for begin_dialog = ON

GO

USE PublisherdDB

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'

create master key encryption by password = 'hello'

create certificate [CertificatoDialogo2]

from file = 'c:\certs\DialogCert2.cer'

with private key (FILE='c:\certs\DialogCert2.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE USER [Proxy::IsDbLookupRequestServiceM02] WITHOUT LOGIN;

GO

create certificate [CertificatoDialogo1]

authorization [Proxy::IsDbLookupRequestServiceM02]

from file = 'c:\certs\DialogCert1.cer'

active for begin_dialog = ON

CREATE REMOTE SERVICE BINDING [RSB::IsDbLookupRequestServiceM02]

TO SERVICE 'IsDbLookupRequestServiceM02'

WITH USER = [Proxy::IsDbLookupRequestServiceM02],

ANONYMOUS = OFF;

GO

CREATE ROUTE [Route::IsDbLookupRequestServiceM02,D516E70B-59D6-4BF4-882A-BDA7ACD6EB07] WITH

SERVICE_NAME = 'IsDbLookupRequestServiceM02',

ADDRESS = 'tcp://PORTATILEXP:4022';

GO

GRANT SEND ON SERVICE::[IsDbLookupResponseService] TO [Proxy::IsDbLookupRequestServiceM02]

GO

USE MASTER

CREATE ENDPOINT [BROKER]

AUTHORIZATION [VIDEOSYSTEM\Simone_Farinea]

STATE=STARTED

AS TCP (LISTENER_PORT = 4033, LISTENER_IP = ALL)

FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED

, MESSAGE_FORWARD_SIZE = 10

, AUTHENTICATION = CERTIFICATE [Certificato2]

, ENCRYPTION = REQUIRED ALGORITHM RC4)

GRANT CONNECT ON ENDPOINT::[BROKER] TO [M02];

Here is the second one:

use master

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'hello'

create master key encryption by password = 'hello'

create certificate [Certificato1]

from file = 'c:\certs\TransportCert1.cer'

with private key (FILE='c:\certs\TransportCert1.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE LOGIN [SIMONEX] WITH PASSWORD = 'wrPqYkr%bm3';

ALTER LOGIN [SIMONEX] DISABLE;

CREATE USER [SIMONEX] FROM LOGIN [SIMONEX];

GO

create certificate [Certificato2]

authorization [SIMONEX]

from file = 'c:\certs\TransportCert2.cer'

active for begin_dialog = ON

GO

USE vsi

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'hello'

create master key encryption by password = 'hello'

create certificate [CertificatoDialogo1]

from file = 'c:\certs\DialogCert1.cer'

with private key (FILE='c:\certs\DialogCert1.pvk',

decryption by password='simone')

active for begin_dialog = ON

CREATE USER [Proxy::IsDbLookupResponseService] WITHOUT LOGIN;

GO

create certificate [CertificatoDialogo2]

authorization [Proxy::IsDbLookupResponseService]

from file = 'c:\certs\DialogCert2.cer'

active for begin_dialog = ON

GRANT SEND ON SERVICE::[IsDbLookupRequestServiceM02] TO [Proxy::IsDbLookupResponseService]

GO

CREATE ROUTE [Route::IsDbLookupResponseService,88EB00C4-8CA9-4B45-9899-677AA70818B1] WITH

SERVICE_NAME = 'IsDbLookupResponseService',

ADDRESS = 'tcp://SIMONEX:4033';

GO

USE MASTER

CREATE ENDPOINT [BROKER]

AUTHORIZATION [VIDEOSYSTEM\Simone_Farinea]

STATE=STARTED

AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)

FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED

, MESSAGE_FORWARD_SIZE = 10

, AUTHENTICATION = CERTIFICATE [Certificato1]

, ENCRYPTION = REQUIRED ALGORITHM RC4)

GRANT CONNECT ON ENDPOINT::[BROKER] TO [SIMONEX];

What's wrong in my code?

Many thanks.

Which server traces the handshake failed error? This error is related to the endpoint authentication, so the problem is from the certificates in [master] ([Certificato1] and [Certificato2]). Please make sure that the right certificates are deployed: look into master.sys.certificates and validate that the certificate thumbprints match between the two instances.

If I'd have to make a wild guess, I'd say: try adding a start/expiration date to the certificates you create. There is a problem with certs created in the eastern UTC time zones that makes the certs unusable for a numbers of hours if a start date is not provided. I believe Italy is GMT-1, so the problem would manifest as the handshake failing for 1 hour after the certs are created, then it would start working.

HTH,
~ Remus

Certificate not found

Hello I haw trouble getting the service broker to work I have 3 instances of SQL servers:

1 “Sender”SQL 2005 Server

2 “Receiver 1” SQLEXPRESS 2005

3 “Recevier 2” SQLEXPRESS 2005

What I wont is to be abele to do is to send a message from “Sender” to “Receiver 1” or “Recevier 2”.

I am abele to send a message from “Sender” to “Receiver 1” but if I send a message to “Receiver 2” I get a dialog security problem I think. If I use profiler I can se in “Receiver 2” the events:

Broker:Connection

Audit Broker Login

Broker:Message Classify

Audit Broker Conversation = Certificate not found

Broker:Message Undeliverable

And I cant find what′s wrong, this Is my scripts for etch instance.

“Sender”

USE master

CREATE CERTIFICATE Cert_ROBOTSRV

WITH SUBJECT = 'Cert_ROBOTSRV_auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_ROBOTSRV TO FILE = 'C:\Cert_ROBOTSRV'

GO

CREATE ENDPOINT SBEndpointServer STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_ROBOTSRV)

GO

CREATE USER andon

CREATE CERTIFICATE Cert_sevapc311_pub AUTHORIZATION andon

FROM FILE = 'C:\Cert_sevapc311'

--DROP CERTIFICATE Cert_Andonpc017_Trans

CREATE CERTIFICATE Cert_Andonpc017_Trans AUTHORIZATION andon

FROM FILE = 'C:\Cert_andonpc017_Trans'

--DROP LOGIN sbLogin

CREATE LOGIN sbLogin

FROM CERTIFICATE Cert_Andonpc017_Trans;

GO

GRANT CONNECT ON ENDPOINT::SBEndpointServer TO [public]

GRANT CONNECT ON ENDPOINT::SBEndpointServer TO andon

GO

-

USE AndonDB

CREATE ROUTE Grafik_sevapc311

WITH SERVICE_NAME = 'Grafik_Service_Recive_sevapc311',

BROKER_INSTANCE = '7C737F42-2DF6-46E7-A6B6-89D1A9608DE2',

ADDRESS = 'TCP://sevapc311:5723'

GO

--DROP ROUTE Grafik_andonpc017

CREATE ROUTE Grafik_Andonpc017

WITH SERVICE_NAME = 'Grafik_Service_Recive_Andonpc017',

BROKER_INSTANCE = 'AE2B294A-B02E-4709-A51E-CFBFD0E478C1',

ADDRESS = 'TCP://192.168.20.106:5723'

GO

CREATE CERTIFICATE Cert_ROBOTSRV_Dialog

WITH SUBJECT = 'Cert_ROBOTSRV_auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_ROBOTSRV_Dialog TO FILE = 'C:\Cert_ROBOTSRV_Dialog'

GO

CREATE CERTIFICATE Cert_sevapc311_pub_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_sevapc311_Dialog'

--DROP CERTIFICATE Cert_andonpc017_Dialog

CREATE CERTIFICATE Cert_Andonpc017_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_andonpc017_Dialog'

GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO andon

CREATE USER sbLogin

GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO sbLogin

GO

--GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

--TO [VADERSTAD\vrobot]

--GO

-- Grant RECEIVE permission on the queue.

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO andon

GO

--DROP REMOTE SERVICE BINDING Grafik_sevap

CREATE REMOTE SERVICE BINDING Grafik_sevap

TO SERVICE 'Grafik_Service_Recive_sevapc311'

WITH USER = andon

GO

--DROP REMOTE SERVICE BINDING Grafik_andonpc017

CREATE REMOTE SERVICE BINDING Grafik_andonpc017

TO SERVICE 'Grafik_Service_Recive_Andonpc017'

WITH USER = andon

“Receiver 1”

use master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

CREATE CERTIFICATE Cert_sevapc311

WITH SUBJECT = 'Cert_sevapc311_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_sevapc311 To FILE = 'C:\Cert_sevapc311'

CREATE ENDPOINT SBEndpointklient STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_sevapc311)

CREATE CERTIFICATE Cert_ROBOTSRV_pub AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV';

GRANT CONNECT ON ENDPOINT::SBEndpointklient to andon

-

use KlientDB

GRANT SEND ON SERVICE::[Grafik_Service_Recive_sevapc311]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_sevapc311]

TO andon

GO

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

CREATE Route Grafik_ROBOTSRV

WITH

SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV',

BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481',

ADDRESS = 'TCP://ROBOTSRV:5723'

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

CREATE CERTIFICATE Cert_sevapc311_Dialog

WITH SUBJECT = 'Cert_sevapc311_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_sevapc311_Dialog To FILE = 'C:\Cert_sevapc311_Dialog'

CREATE CERTIFICATE Cert_ROBOTSRV_pub_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV_Dialog';

“Receiver 2”

use master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

--DROP CERTIFICATE Cert_Andonpc017_Trans

CREATE CERTIFICATE Cert_Andonpc017_Trans

WITH SUBJECT = 'Cert_Andonpc017_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_Andonpc017_Trans To FILE = 'F:\Cert_Andonpc017_Trans'

CREATE ENDPOINT SBEndpointklient STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_Andonpc017_Trans)

--DROP USER andon

CREATE USER andon

--DROP CERTIFICATE Cert_ROBOTSRV

CREATE CERTIFICATE Cert_ROBOTSRV AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV';

CREATE LOGIN sbLogin

FROM CERTIFICATE Cert_ROBOTSRV;

GO

GRANT CONNECT ON ENDPOINT::SBEndpointklient TO [public]

--Select * from sys.certificates

use KlientDB

create user andon

Grant SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017] to [Public]

GRANT SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_Andonpc017]

TO andon

GO

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

--DROP Route Grafik_ROBOTSRV

CREATE Route Grafik_ROBOTSRV

WITH

SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV',

BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481',

ADDRESS = 'TCP://ROBOTSRV:5723'

--Dialog S?kerhet

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

--Drop CERTIFICATE Cert_Andonpc017_Dialog

CREATE CERTIFICATE Cert_Andonpc017_Dialog

WITH SUBJECT = 'Cert_Andonpc017_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_Andonpc017_Dialog To FILE = 'F:\Cert_Andonpc017_Dialog'

--Drop CERTIFICATE Cert_ROBOTSRV_Dialog

CREATE CERTIFICATE Cert_ROBOTSRV_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV_Dialog';

The Security Audit:Audit Broker Conversation event details will contain the Issuer Name and Serial Number of the certificate not found (I think they are the UserName and RemoteLogin columns). You can then look up sys.certificates on the sender side to make sure the sender is using the certificates you expect.

HTH,
~ Remus

|||

Ok thanks.

I will look if I can find what′s wrong.

I got it to work when I turned of the dialog security by deleting RSB.

Certificate loading issue - when creating certificate from SQL Server to SQL Server Express on t

Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.

Following script runs fine on SQL Server.

Code Snippet

use master

Create Master Key Encryption BY Password = '45Gme*3^&fwu';

BACKUP MASTER KEY TO FILE = 'C:\ServiceBroker\PrivateKeyMasterB.pvk'

ENCRYPTION BY PASSWORD = '45Gme*3^&fwu'

Create Certificate EndPointCertificateC

WITH Subject = 'C.Server.Local',

START_DATE = '06/01/2006',

EXPIRY_DATE = '01/01/2008'

ACTIVE FOR BEGIN_DIALOG = ON;

BACKUP CERTIFICATE EndPointCertificateC

TO FILE = 'C:\ServiceBroker\EndPointCertificateC.cer'

Following script runs on SQL Server Express:

Code Snippet

Create Certificate EndPointCertificateC

From FILE = 'C:\ServiceBroker\EndPointCertificateC.cer'

WITH PRIVATE KEY (

FILE = 'C:\ServiceBroker\PrivateKeyMasterB.pvk',

DECRYPTION BY PASSWORD = '45Gme*3^&fwu'

);

If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.

Any help is appreciated. Thank you,

This must be a permisssion issue. The SQL Server Express service account does not have access to the .cer and/or .pvk file.|||

We did give permission to $SQLServerExpress to the folders where we generate the files. And all we are trying to perform this using sa account, I am not sure which other permission we are missing.

|||

Folder permissions are not enough. The files created (*.cer and *.pvk) will be ACL-ed by the creator instance to prevent any other account access, overwriting any permsision inherited from the folder permissions. So if for instance the SQL Express is running as NETWORK SERVICE, or as a (domain) user account, then it will not be able to see the files. You must explicitly grant read permissions on the two created files. Why this work on all other machines is probably because on all other machines the SQL Express and the Enterprise instance run as the same account, but not on this machine.

The account you log in as ('sa') has no relevance, all that matter here is the service account.

Certificate loading issue - when creating certificate from SQL Server to SQL Server Express on t

Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.

Following script runs fine on SQL Server.

Code Snippet

use master

Create Master Key Encryption BY Password ='45Gme*3^&fwu';

BACKUP MASTER KEYTOFILE='C:\ServiceBroker\PrivateKeyMasterB.pvk'

ENCRYPTION BY PASSWORD ='45Gme*3^&fwu'

CreateCertificate EndPointCertificateC

WITH Subject ='C.Server.Local',

START_DATE ='06/01/2006',

EXPIRY_DATE ='01/01/2008'

ACTIVE FOR BEGIN_DIALOG =ON;

BACKUPCERTIFICATE EndPointCertificateC

TOFILE='C:\ServiceBroker\EndPointCertificateC.cer'

Following script runs on SQL Server Express:

Code Snippet

CreateCertificate EndPointCertificateC

FromFILE='C:\ServiceBroker\EndPointCertificateC.cer'

WITH PRIVATE KEY(

FILE='C:\ServiceBroker\PrivateKeyMasterB.pvk',

DECRYPTION BY PASSWORD ='45Gme*3^&fwu'

);

If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.

Any help is appreciated. Thank you,

This must be a permisssion issue. The SQL Server Express service account does not have access to the .cer and/or .pvk file.|||

We did give permission to $SQLServerExpress to the folders where we generate the files. And all we are trying to perform this using sa account, I am not sure which other permission we are missing.

|||

Folder permissions are not enough. The files created (*.cer and *.pvk) will be ACL-ed by the creator instance to prevent any other account access, overwriting any permsision inherited from the folder permissions. So if for instance the SQL Express is running as NETWORK SERVICE, or as a (domain) user account, then it will not be able to see the files. You must explicitly grant read permissions on the two created files. Why this work on all other machines is probably because on all other machines the SQL Express and the Enterprise instance run as the same account, but not on this machine.

The account you log in as ('sa') has no relevance, all that matter here is the service account.