Endpoint Authentication

Hi,
I have setup a basic endpoint that exposes a sp that when given a few
parameters, should go and update a record on the database:
/****** Object: Endpoint [ep_UpdateAddressDetails] Script Date: 02/22/2007
15:01:04 ******/
CREATE ENDPOINT [ep_UpdateAddressDetails]
AUTHORIZATION [mydomainname\tgriffiths]
STATE=STARTED
AS HTTP (PATH=N'/sql', PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED),
SITE=N'lfxakl13', CLEAR_PORT = 80, COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'UpdateAddress'(
NAME=N'[testDb].[dbo].[p_tTest_UpdateAddressDetails]'
, SCHEMA=STANDARD
, FORMAT=ALL_RESULTS), BATCHES=ENABLED,
WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'testDb',
NAMESPACE=N'http://lfxakl13/sql/', SCHEMA=STANDARD, CHARACTER_SET=XML)
I can see the wsdl from a web browser, however when I go and setup a HTTP
Connection in VS2005, I put in my URL as : http://lfxakl13/sql and then press
test and it comes back with "the remote server returned an error: (401)
Unauthorized."
So I presume this is just a permissions issue? However I am unsure what I
need to apply permissions on, as you can see from the statement above, I have
given AUTHORIZATION to my username "tgriffiths". I have also run a seperate
grant connect priviledges for me - but still not difference in the response
from VS2005.
I am a local admin on this machine and my user is authorized in the above
statement, along with me running a specific grant connect on this endpoint. I
am also a db_owner of this database, not to mention being part of the
sysadmin group.
Can anyone assist in getting this going as I am not sure where to look from
here.
Thanks in advance
Troy
Hi Troy,
Please make sure in your Visual Studio application that you are setting the
user credentials to use for the connection.
Example:
proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
For additional details or options, please refer to the following MSDN article:
http://msdn2.microsoft.com/en-us/library/ms175929.aspx
Jimmy
"Troy" wrote:

> Hi,
> I have setup a basic endpoint that exposes a sp that when given a few
> parameters, should go and update a record on the database:
> /****** Object: Endpoint [ep_UpdateAddressDetails] Script Date: 02/22/2007
> 15:01:04 ******/
> CREATE ENDPOINT [ep_UpdateAddressDetails]
> AUTHORIZATION [mydomainname\tgriffiths]
> STATE=STARTED
> AS HTTP (PATH=N'/sql', PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED),
> SITE=N'lfxakl13', CLEAR_PORT = 80, COMPRESSION=DISABLED)
> FOR SOAP (
> WEBMETHOD 'UpdateAddress'(
> NAME=N'[testDb].[dbo].[p_tTest_UpdateAddressDetails]'
> , SCHEMA=STANDARD
> , FORMAT=ALL_RESULTS), BATCHES=ENABLED,
> WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
> SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'testDb',
> NAMESPACE=N'http://lfxakl13/sql/', SCHEMA=STANDARD, CHARACTER_SET=XML)
>
> I can see the wsdl from a web browser, however when I go and setup a HTTP
> Connection in VS2005, I put in my URL as : http://lfxakl13/sql and then press
> test and it comes back with "the remote server returned an error: (401)
> Unauthorized."
> So I presume this is just a permissions issue? However I am unsure what I
> need to apply permissions on, as you can see from the statement above, I have
> given AUTHORIZATION to my username "tgriffiths". I have also run a seperate
> grant connect priviledges for me - but still not difference in the response
> from VS2005.
> I am a local admin on this machine and my user is authorized in the above
> statement, along with me running a specific grant connect on this endpoint. I
> am also a db_owner of this database, not to mention being part of the
> sysadmin group.
> Can anyone assist in getting this going as I am not sure where to look from
> here.
> Thanks in advance
> Troy
>
|||Hi, Thanks Jimmy for your response.
I had already confirmed the proxy userdetails etc, but same error.
After playing around a little more I have managed to get it working - and
realise now that the Endpoints within SQL only allow HTTP Get's rather than
Posts - and this is most likely where this error is coming from *maybe*
Anyhow, I can at least select my web service now!
Thanks
Troy
"Jimmy Wu [MSFT]" wrote:
[vbcol=seagreen]
> Hi Troy,
> Please make sure in your Visual Studio application that you are setting the
> user credentials to use for the connection.
> Example:
> proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
> For additional details or options, please refer to the following MSDN article:
> http://msdn2.microsoft.com/en-us/library/ms175929.aspx
> Jimmy
> "Troy" wrote:

Endpoint Authentication

Hi,
I have setup a basic endpoint that exposes a sp that when given a few
parameters, should go and update a record on the database:
/****** Object: Endpoint [ep_UpdateAddressDetails] Script Date: 02/22/2007
15:01:04 ******/
CREATE ENDPOINT [ep_UpdateAddressDetails]
AUTHORIZATION [mydomainname\tgriffiths]
STATE=STARTED
AS HTTP (PATH=N'/sql', PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED),
SITE=N'lfxakl13', CLEAR_PORT = 80, COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'UpdateAddress'(
NAME=N'[testDb].[dbo].[p_tTest_UpdateAddressDetails]'
, SCHEMA=STANDARD
, FORMAT=ALL_RESULTS), BATCHES=ENABLED,
WSDL=N'[master].[sys]. [sp_http_generate_wsdl_defaultcomplexors
imple]',
SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'testDb',
NAMESPACE=N'http://lfxakl13/sql/', SCHEMA=STANDARD, CHARACTER_SET=XML)
I can see the wsdl from a web browser, however when I go and setup a HTTP
Connection in VS2005, I put in my URL as : http://lfxakl13/sql and then pres
s
test and it comes back with "the remote server returned an error: (401)
Unauthorized."
So I presume this is just a permissions issue? However I am unsure what I
need to apply permissions on, as you can see from the statement above, I hav
e
given AUTHORIZATION to my username "tgriffiths". I have also run a seperate
grant connect priviledges for me - but still not difference in the response
from VS2005.
I am a local admin on this machine and my user is authorized in the above
statement, along with me running a specific grant connect on this endpoint.
I
am also a db_owner of this database, not to mention being part of the
symin group.
Can anyone assist in getting this going as I am not sure where to look from
here.
Thanks in advance
TroyHi Troy,
Please make sure in your Visual Studio application that you are setting the
user credentials to use for the connection.
Example:
proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
For additional details or options, please refer to the following MSDN articl
e:
http://msdn2.microsoft.com/en-us/library/ms175929.aspx
Jimmy
"Troy" wrote:

> Hi,
> I have setup a basic endpoint that exposes a sp that when given a few
> parameters, should go and update a record on the database:
> /****** Object: Endpoint [ep_UpdateAddressDetails] Script Date: 02/22/2007
> 15:01:04 ******/
> CREATE ENDPOINT [ep_UpdateAddressDetails]
> AUTHORIZATION [mydomainname\tgriffiths]
> STATE=STARTED
> AS HTTP (PATH=N'/sql', PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED),
> SITE=N'lfxakl13', CLEAR_PORT = 80, COMPRESSION=DISABLED)
> FOR SOAP (
> WEBMETHOD 'UpdateAddress'(
> NAME=N'[testDb].[dbo].[p_tTest_UpdateAddressDetails]'
> , SCHEMA=STANDARD
> , FORMAT=ALL_RESULTS), BATCHES=ENABLED,
> WSDL=N'[master].[sys]. [sp_http_generate_wsdl_defaultcomplexors
imple]',
> SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'testDb',
> NAMESPACE=N'http://lfxakl13/sql/', SCHEMA=STANDARD, CHARACTER_SET=XML)
>
> I can see the wsdl from a web browser, however when I go and setup a HTTP
> Connection in VS2005, I put in my URL as : http://lfxakl13/sql and then pr
ess
> test and it comes back with "the remote server returned an error: (401)
> Unauthorized."
> So I presume this is just a permissions issue? However I am unsure what I
> need to apply permissions on, as you can see from the statement above, I h
ave
> given AUTHORIZATION to my username "tgriffiths". I have also run a seperat
e
> grant connect priviledges for me - but still not difference in the respons
e
> from VS2005.
> I am a local admin on this machine and my user is authorized in the above
> statement, along with me running a specific grant connect on this endpoint
. I
> am also a db_owner of this database, not to mention being part of the
> symin group.
> Can anyone assist in getting this going as I am not sure where to look fro
m
> here.
> Thanks in advance
> Troy
>|||Hi, Thanks Jimmy for your response.
I had already confirmed the proxy userdetails etc, but same error.
After playing around a little more I have managed to get it working - and
realise now that the Endpoints within SQL only allow HTTP Get's rather than
Posts - and this is most likely where this error is coming from *maybe*
Anyhow, I can at least select my web service now!
Thanks
Troy
"Jimmy Wu [MSFT]" wrote:
> Hi Troy,
> Please make sure in your Visual Studio application that you are setting th
e
> user credentials to use for the connection.
> Example:
> proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
> For additional details or options, please refer to the following MSDN arti
cle:
> http://msdn2.microsoft.com/en-us/library/ms175929.aspx
> Jimmy
> "Troy" wrote:
>

Encryption and the client

I have a basic understanding of the encryption using T-SQL, but is all of this at the T-SQL level? Like can a client have a certificate, be sent the encrypted value (that was encrypted on SQL Server), and using the same cert that is on the server decrypt the data?

Is there a good article/book that covered encryption in detail that you can suggest? Or is this something that you would build in .NET?

If your concern is to protect data in transit, I would strongly recommend you to use SSL to protect the data between your server and the client.

If your goal is to protect data at rest, but in such a way that the protected data cannot be decrypted by the server (i.e. the decryption key is never stored/used in the server hosting SQL Server) you can use .Net to protect the data directly, but all the key management should be on your client application.

Typically, for normal data I don’t recommend using a model where you use public keys to protect data directly on SQL Server, and use the private key on the client to decrypt it (or vice versa).

The main reasons are that your application will still have to do most of the key management (private/public keys), the performance of asymmetric key encryption is orders of magnitude slower than symmetric key encryption and that in SQL Server the limit for encrypting data using an asymmetric key is 1 block (based on the private key modulus, for the self signed certificates SQL Server generates this means you can only encrypt up to ~117 bytes of plaintext).

After saying that, there may be some cases where this mechanism may actually work better for your particular needs. You can find one sample on how to use the .Net framework to encrypt data and decrypt it on SQL Server using asymmetric keys: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384472&SiteID=1

I hope this information can help you.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Actually my question was largely academic in nature, as I was trying to get my head straight on a few topics with SQL Server for a client. I had a few preconcieved notions about how things worked with encryption.

Thanks again (you have helped me before :)!

encrypt/ decrypt fields or VB function "StrReverse".

I need to encrypt some fields on insert/ update and decrypt on select.
I have developed a function in visual basic but I need it to be a SQL Server
function. Is there any built-in functions availables to encrypt/ decrypt
fields or a substitution to visual basic's function "StrReverse" .
Kind regards
Khurram ButtThe REVERSE function in T-SQL is equivalent to the StrReverse function
from Visual Basic.
In SQL Server 2000, there are some undocumented functions for one-way
encryption of passwords (and other similar data): pwdencrypt and
pwdcompare. See this page (for example):
http://weblogs.asp.net/bdesmond/arc...8/15/24177.aspx
In SQL Server 2005, things are much better: there are functions for
encryption and decryption (using symmetric or asymmetric keys) and also
for digital signing.
Razvan|||No there are no such built-in functions in SQL Server 2000, probably you
have to handle this in your VB application or by creating extended stored
procedures.
"Khurram Shahzad" <Khurram.Shahzad@.360training.com> wrote in message
news:eFFHjbXrFHA.1236@.TK2MSFTNGP10.phx.gbl...
>I need to encrypt some fields on insert/ update and decrypt on select.
> I have developed a function in visual basic but I need it to be a SQL
> Server function. Is there any built-in functions availables to encrypt/
> decrypt fields or a substitution to visual basic's function "StrReverse" .
> Kind regards
> Khurram Butt
>