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 speed?

Has anyone started using encryption yet? Is it noticably slower than not using it? For the record Im not refering too column encryption, but "network" (not sure what else to call it) encryption when it's encrypted between SQL Server and the client.We did some encryption testing a while back because a client wanted at-rest encryption. Hardware solutions between the storage and sql server seem to have the least impact on data delivery, while software solutions eat up mega cpu cycles.

There was one product that used a card to do the hardware encryption, but also had software installed as a backup in case of h/w failure so it could at least limp along until the h/w could be replaced.

Either way, there will be some performance loss. How much depends on your system(s). The vendors were more than willing to give us test time.sql

Encrypted value shown as '?' in a column of type varchar

Dear All,

I inserted a record in table on DB created on SQLServer 2005 and found out that the one of the column values is shown as '?' instead of showing the encrypted value that I sent with the insert statement.3

............................ Can anyone tell me how to get rid of this?

Thanks and regards,

Z Z.

How are you encrypting the data and how are you retrieving it?|||Thanks for your reply. Actually I'm using only one-way encryption and seen these '?' through SQL Server Management Studio by directly viewing the table contents.|||So what are you expecting to see returned if you are using one way encryption?|||

I was expecting to see encrypted value when I opened the database table directly from within the SQL Server Management Studio. Instead I found '?' only. Anyway, I am done with it and used two encryption mechanism. Thanks a lot.

Empty recordsets and artificial records

I'm currently running the following statement that is used in a Crystal
Report. Basically, a record is returned when the T_PAYMENT.amount
has a record in the database based on the value of the T_MULTILIST.code
field. Currently, if there is no record returned, there is no listing
in the report for the given T_MULTILIST.code.

The user now wants a record to be displayed on the report when there is
no record in the database - she wants it to display a value of '$0'
for the given T_MULTILIST.code record. I tried to explain the fact that
is not possible the way things stand at the moment. Basically I need
some type
of case statement that says 'if there is no record returned, create a
single record and set T_PAYMENT.amount = 0' AFTER each query has been
executed.
Anyone have any idea how to accomplish this?

SELECT DISTINCT
'English Language Arts, Grade 1' as Rec_Type, 'English Language Arts
(Consumable)' as Super_Type, '' as Other_Type, 'Continuing Contracts'
as Proc_Type,
T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
T_PAYMENT.amount
FROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETAIL
T_SHIPPING_DETAIL ON
T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)
ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOIN
T_MULTILIST_GRADE T_MULTILIST_GRADE ON
T_MULTILIST.code=T_MULTILIST_GRADE.multilist_code,
T_ORDER, T_REQUISITION, T_REQUISITION_DETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETAIL.order_id AND
T_REQUISITION.id = T_ORDER.requisition_id AND
T_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id AND
T_REQUISITION_DETAIL.latest_record_flag = 1 AND
T_REQUISITION.latest_record_flag = 1 AND
T_ORDER.latest_record_flag = 1
AND (T_MULTILIST.code='1040')
AND (T_MULTILIST.expiration_year >= '2006' )
AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'
AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,
'2006' + '0601' ) )

UNION
SELECT DISTINCT
'English Language Arts, Kindergarten' as Rec_Type, 'English
Language Arts (Consumable)' as Super_Type,
'' as Other_Type, 'Continuing Contracts' as Proc_Type,
T_MULTILIST_GRADE.grade, T_MULTILIST.description,
T_MULTILIST.code, T_PAYMENT.amount
FROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETAIL T_SHIPPING_DETAIL
ON T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)
ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOIN
T_MULTILIST_GRADE T_MULTILIST_GRADE ON
T_MULTILIST.code=T_MULTILIST_GRADE.multilist_code,
T_ORDER, T_REQUISITION, T_REQUISITION_DETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETAIL.order_id AND
T_REQUISITION.id = T_ORDER.requisition_id AND
T_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id AND
T_REQUISITION_DETAIL.latest_record_flag = 1 AND
T_REQUISITION.latest_record_flag = 1 AND
T_ORDER.latest_record_flag = 1
AND (T_MULTILIST.code='0040')
and (T_MULTILIST.expiration_year >= '2006' )
AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'
AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,
'2006' + '0601' ) )

Up to 40 more UNION statements follow the above 2.wgblackmon@.yahoo.com (wgblackmon@.yahoo.com) writes:

Quote:

Originally Posted by

I'm currently running the following statement that is used in a Crystal
Report. Basically, a record is returned when the T_PAYMENT.amount
has a record in the database based on the value of the T_MULTILIST.code
field. Currently, if there is no record returned, there is no listing
in the report for the given T_MULTILIST.code.
>
>
The user now wants a record to be displayed on the report when there is
no record in the database - she wants it to display a value of '$0'
for the given T_MULTILIST.code record. I tried to explain the fact that
is not possible the way things stand at the moment. Basically I need
some type
of case statement that says 'if there is no record returned, create a
single record and set T_PAYMENT.amount = 0' AFTER each query has been
executed.

Sounds like you are looking for an outer join. Now, since I don't know
your tables, or what is being presented, the below may not be the
exact match, but you may be able to get the drift.

SELECT DISTINCT
'English Language Arts, Grade 1' as Rec_Type, 'English Language Arts
(Consumable)' as Super_Type, '' as Other_Type, 'Continuing Contracts'
as Proc_Type,
T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
amount = coalesce(T_PAYMENT.amount, 0)
FROM T_MULTILIST T_MULTILIST
LEFT JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETAIL T_SHIPPING_DETAIL
ON T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)
ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code)
INNER JOIN T_MULTILIST_GRADE T_MULTILIST_GRADE ON
T_MULTILIST.code=T_MULTILIST_GRADE.multilist_code,
T_ORDER, T_REQUISITION, T_REQUISITION_DETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETAIL.order_id AND
T_REQUISITION.id = T_ORDER.requisition_id AND
T_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id AND
T_REQUISITION_DETAIL.latest_record_flag = 1 AND
T_REQUISITION.latest_record_flag = 1 AND
T_ORDER.latest_record_flag = 1
AND (T_MULTILIST.code='1040')
AND (T_MULTILIST.expiration_year >= '2006' )
AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'
AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,
'2006' + '0601' ) )

Permit me that the query is quite messy with it's mix of ANSI-join operators
and comma-style cross-join with the join conditions in the WHERE clause.
With outer joins in the mix, you should rewrite all to use ANSI joins.

Quote:

Originally Posted by

Up to 40 more UNION statements follow the above 2.

40? Ouch! But why? From the two segments you posted, it appears to me
that all that differs is the condition on T_MULTILIST.code.

--
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|||On 5 Jul 2006 09:41:41 -0700, wgblackmon@.yahoo.com wrote:

Quote:

Originally Posted by

>I'm currently running the following statement that is used in a Crystal
>Report. Basically, a record is returned when the T_PAYMENT.amount
>has a record in the database based on the value of the T_MULTILIST.code
>field. Currently, if there is no record returned, there is no listing
>in the report for the given T_MULTILIST.code.

(snip)

Hi wgblackmon,

The statement you gave is horrible, and I'd be surprised if it shows
anything even resembling performance.

If I may assume that the other 40 UNION'ed SELECT statements all
resemble the first two, you have 42 almost equal SELECT statements with
only one code in the WHERE clause and some constants in the SELECT list
different. Why don't you include the 42 relevant codes and the
accompanying values for Rec_Type, Super_Type, Other_Type, and Proc_Type
in a table? Then, yoou can rewrite the complete monster with one single
query.

There are many other things at fault with your query too:
- Think about readability and maintainability: Why do you supply aliases
that are exactly equal to the table's name? Why do you mix "new style"
infixed joins with "old style" joins (using a comma-seperated list of
tables)? Why do you use nested INNER JOINs? Why don't you stick to one
style for using newlines and indentation to make your queries more
readable?
- Think about performance: Why do you use >'2006' + '0601' << to
denote a fixed date? If you use >'20060601' <<, it can be converted at
compile-time. Now, you're forcing string concatenation and conversion at
execution time. It gets even worse in the complicated datetime formula
that can be replaced with '20070601'. Also, why do you use "UNION"
instead of "UNION ALL", forcing SQL Server to search for duplicates if
the constants in the 42 SELECT lists are distinct anyway? And do you
really need a DISTINCT on the individual queries? It's often a token of
bad design.

Quote:

Originally Posted by

>The user now wants a record to be displayed on the report when there is
>no record in the database - she wants it to display a value of '$0'
>for the given T_MULTILIST.code record. I tried to explain the fact that
>is not possible the way things stand at the moment.

If you add the extra table suggested above, fulfilling this requirement
is as easy as changing an INNER JOIN to an OUTER JOIN and adding some
COALESCE functions in the SELECT list!

Here's a quick stab (retaining the DISTINCT for now, but do check if you
can leave it out). I have already added the user's wish.

First, set up and fill a table of codes and type descriptions:

CREATE TABLE dbo.CodeList
(Code char(4) NOT NULL PRIMARY KEY,
Rec_Type varchar(40) NOT NULL,
Super_Type varchar(40) NOT NULL,
Other_Type varchar(40) NOT NULL,
Proc_Type varchar(40) NOT NULL)
go
INSERT INTO CodeList (Code, Rec_Type, Super_Type, Other_Type, Proc_Type)
SELECT '1040', 'English Language Arts, Grade 1',
'English Language Arts (Consumable)', '', 'Continuing Contracts'
UNION ALL
SELECT '0040', 'English Language Arts, Kindergarten',
'English Language Arts (Consumable)', '', 'Continuing Contracts'
-- etc for the other codes
go

Now, attempt to rewrite your query.

SELECT DISTINCT c.Rec_Type, c.Super_Type, c.Other_Type, c.Proc_Type,
mg.grade, m.description, m.code, p.amount
FROM T_PAYMENT AS p
INNER JOIN T_SHIPPING_DETAIL AS sd
ON p.transaction_id = sd.transaction_id
INNER JOIN T_MULTILIST AS m
ON m.code = sd.multilist_code
AND m.expiration_year >= '2006'
INNER JOIN T_MULTILIST_GRADE mg
ON m.code = mg.multilist_code
INNER JOIN T_ORDER AS o
ON o.id = sd.order_id
AND o.latest_record_flag = 1
INNER JOIN T_REQUISITION AS r
ON r.id = o.requisition_id
AND r.latest_record_flag = 1
AND r.requested_shipment_date >= '20060601'
AND r.requested_shipment_date < '20070601'
INNER JOIN T_REQUISITION_DETAIL AS rd
ON rd.requisition_id = r.id
AND rd.latest_record_flag = 1
RIGHT OUTER JOIN dbo.CodeList AS c
ON c.Code = m.code

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP|||On Thu, 06 Jul 2006 00:26:27 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

>The statement you gave is horrible, and I'd be surprised if it shows
>anything even resembling performance.

From the original post, I would wonder if it was generated by Crystal
Reports. Code that bad does often come from such sources.

Roy Harvey
Beacon Falls, CT