401: Unauthorized error in spatial select statement

Question

401: Unauthorized error in spatial select statement

vikysaran

SSC-Addicted

Points: 479
More actions
August 27, 2010 at 3:09 am

#237250

Hi,
I'm new to Spatial coding. i found a sample code at http://msdn.microsoft.com/en-us/magazine/dd434647.aspx and trying to run its 'catalog_geocoder.sql' file. All is going well but on the select statement it is giving error bellow-
Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "geocode":
System.Net.WebException: The request failed with HTTP status 401: Unauthorized.
System.Net.WebException:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at MappointGeocoder.MapPointRef.FindServiceSoap.FindAddress(FindAddressSpecification specification)
at Spatial.MpGeocoder.Geocode(String streetAddress)[font="Courier New"]
Here I'm pasting the sql code
alter database pubs set trustworthy on
go
use pubs
go
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
create assembly geocode from 'C:\temp\MappointGeocoder.dll'
with permission_set = external_access
go
create assembly ser from 'C:\temp\MappointGeocoder.XmlSerializers.dll'
with permission_set = safe
go
create function dbo.geocode (@address nvarchar(4000))
returns nvarchar(100)
as
external name geocode.[Spatial.MpGeocoder].Geocode
go
select dbo.Geocode('One Microsoft Way, Redmond, WA 98052');
go
create function GeocodePoint(@address varchar(100))
returns geography
as
begin
declare @g geography;
select @g = geography::STPointFromText(dbo.Geocode(@address), 0);
return @g
end
go
select dbo.GeocodePoint('One Microsoft Way, Redmond, WA 98052').ToString();
go
Also i placed the files into c:/Temp as i mentioned the path above. Here both select statements are giving same error.
Please help ASAP :cool::cool::cool:

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

The error suggests you're behind a proxy server that requires authentication (Such as Microsoft ISA Server) and you're trying to access an external web service.

So in your CLR code, you would need to add code to provide this authentication (or get the default settings from the logged on user, bearing in mind this is the SQL Service Account).

I've never experimented with it and I doubt it causes this error, but your CLR assembly also needs to be created as at least EXTERNAL_ACCESS (not SAFE) for it to be able to access external resources such as a web service.

vikysaran SSC-Addicted Points: 479 More actions · Answer 2

HowardW (8/27/2010)
The error suggests you're behind a proxy server that requires authentication (Such as Microsoft ISA Server) and you're trying to access an external web service.
So in your CLR code, you would need to add code to provide this authentication (or get the default settings from the logged on user, bearing in mind this is the SQL Service Account).
I've never experimented with it and I doubt it causes this error, but your CLR assembly also needs to be created as at least EXTERNAL_ACCESS (not SAFE) for it to be able to access external resources such as a web service.

could you guide me how to add code to provide authentication or how to get settings from logged user? Please help if possible. The code is same as i pasted above. Also, CLR assembly is already set to External Access

HowardW One Orange Chip Points: 29109 More actions · Answer 3

Actually, it's more likely to be a 407 if it's proxy authentication required.

You do have a Bing Maps Platform account and are providing the authentication for this as per the code sample, yes?

Note: In order to use the code described in this article, you must have an active Bing Maps Platform account. To request a developer account, please go to the Bing Maps Developer Account Request page.

And you're passing these credentials into your CLR:

private MapPointService.FindServiceSoap findService;

public RooftopGeocoder()

{

findService = new MapPointService.FindServiceSoap();

findService.Credentials = new System.Net.NetworkCredential("username", "password");

findService.PreAuthenticate = true;

}

vikysaran SSC-Addicted Points: 479 More actions · Answer 4

HowardW (8/27/2010)
Actually, it's more likely to be a 407 if it's proxy authentication required.
You do have a Bing Maps Platform account and are providing the authentication for this as per the code sample, yes?
Note: In order to use the code described in this article, you must have an active Bing Maps Platform account. To request a developer account, please go to the Bing Maps Developer Account Request page.
And you're passing these credentials into your CLR:
private MapPointService.FindServiceSoap findService;
public RooftopGeocoder()
{
findService = new MapPointService.FindServiceSoap();
findService.Credentials = new System.Net.NetworkCredential("username", "password");
findService.PreAuthenticate = true;
}

Thanks for your quick reply.

Let me know that the username and password are same as my account credentials at bing Dev center? Like my username there is vikysaran@hotmail.com and password will be same as password of this login id?

vikysaran SSC-Addicted Points: 479 More actions · Answer 5

Ok,

i understand it

the credentials are here at https://mappoint-css.live.com/cscv3/Css.aspx

I got confused.

Now the username will be account id and password whatever we set here through manage password section, right?

vikysaran SSC-Addicted Points: 479 More actions · Answer 6

vikysaran

SSC-Addicted

Points: 479

August 28, 2010 at 3:01 am

#1213394

I'm still not able to run the app....

401: Unauthorized error in spatial select statement

Cookies on SQLServerCentral