Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client

I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL 2005
web services using integrated authentication.
In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
same workstations as the client and connectivity works fine.
In testing, the windows app is begin deployed to a Windows 2000 Server
workstation and SQL Server 2005 is installed on a Windows 2003 Server. Both
are on the same domain. The user logged into the client station has admin
access on the server and dbo access (and in this case created the endpoints).
However, when the client attempts to access the server, 401 Access Denied is
returned.
The server specifies Integrated Authentication as follows:
CREATE ENDPOINT RMS
STATE = STARTED
AS HTTP (
SITE = 'SERVER',
PATH = '/sql/app1',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
The client adds the credentials to the cache before the call:
ws.Credentials =
System.Net.CredentialCache.DefaultCredentials;
I think I've covered everything here and from what I have read, the error
indicates that the user is not being authenticated (vs an issue with any
specific SQL object permissions).
Any ideas on what might be happening here and how to fix?
Thanks
-Luther
Hi Luther,
welcome to MSDN newsgroup.
As for the SQL2005's endpoint SOAP webservice, after you created the
endpoint, have you tried using IE browser to view the WSDL document of that
endpoint service? e.g:
http://servername/sql/test_endpoint?WSDL
If this also not work, the problem is likely due to the serverside
setting... If that works, we may need to check the client side code...
Also, you can print out the thread's current secuirty principal to see
whether it is the correct domain account of the server machine ...
In addition , you can also try adding the "Authorizing" setting to grant
certain user/role the permission as below:
AUTHORIZATION sa -- with optional authorization for owner
=======================
CREATE ENDPOINT zipcodes -- create the Http Endpoint
AUTHORIZATION sa -- with optional authorization for owner
STATE = STARTED -- the state of the endpoint
AS HTTP ( -- can be http, tcp, ...
path='/sql/zipcodes', -- the virtual path
AUTHENTICATION=(INTEGRATED), -- type of authentication
PORTS=(CLEAR), -- which ports (clear=all)
SITE ='*' -- site can be '*" or 'myserver'
)
FOR SOAP( -- type of protocol, in this case, SOAP for a webservice
WEBMETHOD 'http://tempuri.org/'.'DistanceBetweenzZipCodes'
-- define the webmethod(s) and fully qualified sproc
(name='zipcodes.dbo.DistanceBetweenZipcodes',
SCHEMA = STANDARD ),
======================
If there're any other findings, please feel free to post here.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Thread-Topic: Getting 401 Access denied w/ Endpoint in SQL 2005 from C#
client
| thread-index: AcYMnwmiqttQJ9quQhelBHxCetS8Eg==
| X-WBNR-Posting-Host: 63.211.139.67
| From: "=?Utf-8?B?THV0aGVyIE1pbGxlcg==?=" <lex3001@.community.nospam>
| Subject: Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client
| Date: Thu, 29 Dec 2005 09:41:02 -0800
| Lines: 39
| Message-ID: <910F2F43-FF93-4110-A162-2C04A46F4451@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.connect:46496
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL
2005
| web services using integrated authentication.
|
| In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
| same workstations as the client and connectivity works fine.
|
| In testing, the windows app is begin deployed to a Windows 2000 Server
| workstation and SQL Server 2005 is installed on a Windows 2003 Server.
Both
| are on the same domain. The user logged into the client station has admin
| access on the server and dbo access (and in this case created the
endpoints).
|
| However, when the client attempts to access the server, 401 Access Denied
is
| returned.
|
| The server specifies Integrated Authentication as follows:
|
| CREATE ENDPOINT RMS
| STATE = STARTED
| AS HTTP (
| SITE = 'SERVER',
| PATH = '/sql/app1',
| AUTHENTICATION = (INTEGRATED),
| PORTS=(CLEAR)
| )
|
| The client adds the credentials to the cache before the call:
|
| ws.Credentials =
| System.Net.CredentialCache.DefaultCredentials;
|
| I think I've covered everything here and from what I have read, the error
| indicates that the user is not being authenticated (vs an issue with any
| specific SQL object permissions).
|
| Any ideas on what might be happening here and how to fix?
|
| Thanks
| -Luther
|
|

Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client

I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL 200
5
web services using integrated authentication.
In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
same workstations as the client and connectivity works fine.
In testing, the windows app is begin deployed to a Windows 2000 Server
workstation and SQL Server 2005 is installed on a Windows 2003 Server. Both
are on the same domain. The user logged into the client station has admin
access on the server and dbo access (and in this case created the endpoints)
.
However, when the client attempts to access the server, 401 Access Denied is
returned.
The server specifies Integrated Authentication as follows:
CREATE ENDPOINT RMS
STATE = STARTED
AS HTTP (
SITE = 'SERVER',
PATH = '/sql/app1',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
The client adds the credentials to the cache before the call:
ws.Credentials =
System.Net.CredentialCache.DefaultCredentials;
I think I've covered everything here and from what I have read, the error
indicates that the user is not being authenticated (vs an issue with any
specific SQL object permissions).
Any ideas on what might be happening here and how to fix?
Thanks
-LutherHi Luther,
welcome to MSDN newsgroup.
As for the SQL2005's endpoint SOAP webservice, after you created the
endpoint, have you tried using IE browser to view the WSDL document of that
endpoint service? e.g:
http://servername/sql/test_endpoint?WSDL
If this also not work, the problem is likely due to the serverside
setting... If that works, we may need to check the client side code...
Also, you can print out the thread's current secuirty principal to see
whether it is the correct domain account of the server machine ...
In addition , you can also try adding the "Authorizing" setting to grant
certain user/role the permission as below:
AUTHORIZATION sa -- with optional authorization for owner
=======================
CREATE ENDPOINT zipcodes -- create the Http Endpoint
AUTHORIZATION sa -- with optional authorization for owner
STATE = STARTED -- the state of the endpoint
AS HTTP ( -- can be http, tcp, ...
path='/sql/zipcodes', -- the virtual path
AUTHENTICATION=(INTEGRATED), -- type of authentication
PORTS=(CLEAR), -- which ports (clear=all)
SITE ='*' -- site can be '*" or 'myserver'
)
FOR SOAP( -- type of protocol, in this case, SOAP for a webservice
WEBMETHOD 'http://tempuri.org/'.'DistanceBetweenzZipCodes'
-- define the webmethod(s) and fully qualified sproc
(name='zipcodes.dbo.DistanceBetweenZipcodes',
SCHEMA = STANDARD ),
======================
If there're any other findings, please feel free to post here.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Thread-Topic: Getting 401 Access denied w/ Endpoint in SQL 2005 from C#
client
| thread-index: AcYMnwmiqttQJ9quQhelBHxCetS8Eg==
| X-WBNR-Posting-Host: 63.211.139.67
| From: "examnotes" <lex3001@.community.nospam>
| Subject: Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client
| Date: Thu, 29 Dec 2005 09:41:02 -0800
| Lines: 39
| Message-ID: <910F2F43-FF93-4110-A162-2C04A46F4451@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.connect:46496
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL
2005
| web services using integrated authentication.
|
| In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
| same workstations as the client and connectivity works fine.
|
| In testing, the windows app is begin deployed to a Windows 2000 Server
| workstation and SQL Server 2005 is installed on a Windows 2003 Server.
Both
| are on the same domain. The user logged into the client station has admin
| access on the server and dbo access (and in this case created the
endpoints).
|
| However, when the client attempts to access the server, 401 Access Denied
is
| returned.
|
| The server specifies Integrated Authentication as follows:
|
| CREATE ENDPOINT RMS
| STATE = STARTED
| AS HTTP (
| SITE = 'SERVER',
| PATH = '/sql/app1',
| AUTHENTICATION = (INTEGRATED),
| PORTS=(CLEAR)
| )
|
| The client adds the credentials to the cache before the call:
|
| ws.Credentials =
| System.Net.CredentialCache.DefaultCredentials;
|
| I think I've covered everything here and from what I have read, the error
| indicates that the user is not being authenticated (vs an issue with any
| specific SQL object permissions).
|
| Any ideas on what might be happening here and how to fix?
|
| Thanks
| -Luther
|
|

Get Windows NT/XP Logon ID in SQL Server

Is there a way to get the Windows NT/XP Logon ID in SQL Server if the
user logs on as "sa"?
SUSER_SNAME () returns "sa"
Thanks
RonRon
What did you mean by Logon ID ? A process id ?
"RonL" <sal_paradise_93@.yahoo.com> wrote in message
news:1144536622.162777.312290@.u72g2000cwu.googlegroups.com...
> Is there a way to get the Windows NT/XP Logon ID in SQL Server if the
> user logs on as "sa"?
> SUSER_SNAME () returns "sa"
> Thanks
> Ron
>|||RonL (sal_paradise_93@.yahoo.com) writes:
> Is there a way to get the Windows NT/XP Logon ID in SQL Server if the
> user logs on as "sa"?
> SUSER_SNAME () returns "sa"
No. And keep in mind that there may not even be a login ID. The user
could log in from a Unix box.
This a good argument why you should run with Windows Authentication only,
or at least why you should do something like:
DECLARE @.pw char(36)
SELECT @.pw = convert(char(36), newid())
PRINT @.pw
EXEC sp_password @.pw, NULL, sa
Run this script, and without looking at the output, print it, and lock it
into a safe.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm trying to pick up the Windows Logon Id in a Trigger. I can retrieve
the Windows Logon Id in code ASP.NET or Windows but I'm trying to
retrieve it in a Trigger.
I've seen code that at connect time concatenates the UserId with the
Computer Name to create the Workstation ID.
e.g.,
Server = .. Initial Catalog= .. Workstation ID= computerName + userid
Then to retrieve the Userid they execute sp_who and retrieve the value
from the column HOSTNAME.
Unfortunately Windows Authentication is not an option.
Thanks
Ron|||RonL (sal_paradise_93@.yahoo.com) writes:
> I'm trying to pick up the Windows Logon Id in a Trigger. I can retrieve
> the Windows Logon Id in code ASP.NET or Windows but I'm trying to
> retrieve it in a Trigger.
> I've seen code that at connect time concatenates the UserId with the
> Computer Name to create the Workstation ID.
> e.g.,
> Server = .. Initial Catalog= .. Workstation ID= computerName + userid
> Then to retrieve the Userid they execute sp_who and retrieve the value
> from the column HOSTNAME.
Yes, the client can be co-operative and some agreed connection property
to the information you want to pass. WSID or AppName are the choises
that are closest at hand. SET CONTEXT_INFO is also an option.
Running sp_who to get the hostname is a serious overkill. You can use
the built-in function host_name(). (And app_name() if you use the
application name.)
However, there is no way that SQL Server can get this information on
its own, so if SQL Server cannot trust the client, you are out of luck.
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|||If NT authentication is not an option, use SQL Server authentcation, but
never allow users to connect as SA. Create as many SQL user roles as needed
then create as many database users and put them in appropriate roles. Only
give the users the privileges they need to execute individual business tasks
- why would they need the power to drop/create/alter SQL objects etc.?
ML
http://milambda.blogspot.com/

Get value from Report

There is a textbox property where I can set the Jump to URL value equal to
any field from the report. I currently have a windows application with an IE
web control embedded in the windows form. How can I get the value of a
selected field in the report outside of reporting services? Is it possible to
create a web service and pass the value over to it and retrieve it that way
or is there a better way to do this? Thanks for any input.I am not sure what you want to do here. If you want the value of a
selected field from your app to be passed to a report you can pass it
as a parameter to the report.
if you want a value from the report to be passed into your app then you
can pass the value from your report into a custom assembly, which you
can use in your app.|||I would like the value passed from the report to the calling app. Can you
point me to any examples (preferably c#) re. passing values from report into
a custom assembly? Thanks.
"RN" wrote:
> I am not sure what you want to do here. If you want the value of a
> selected field from your app to be passed to a report you can pass it
> as a parameter to the report.
> if you want a value from the report to be passed into your app then you
> can pass the value from your report into a custom assembly, which you
> can use in your app.
>|||The custom assembly would be a C# project with a method in it that
accepts the value you want to pass to your app, which you can save in a
global variable or collection and use in your app. Please refer to
Custom Assemblies in books online for information about how to
reference this assembly in your report and how to call methods in it.
El Presidente wrote:
> I would like the value passed from the report to the calling app.
Can you
> point me to any examples (preferably c#) re. passing values from
report into
> a custom assembly? Thanks.
> "RN" wrote:
> > I am not sure what you want to do here. If you want the value of a
> > selected field from your app to be passed to a report you can pass
it
> > as a parameter to the report.
> > if you want a value from the report to be passed into your app then
you
> > can pass the value from your report into a custom assembly, which
you
> > can use in your app.
> >
> >

Get UserName of Windows Account for Report Parameter

We would like to automatically pass the username as a parameter to reports. By username, I mean the username from the Windows account used to access the virtual directory containing the report (which uses Integrated Windows Authentication). Sort of like the User.Identity.Name property in ASP.NET. Is this going to be possible?

Thanks

Hi ALFKI.
Have you tried Globasl!UserID? ...or was it User!UserID?...damn, it slipped my mind; but I guess it'll help you.
Regards|||

User!UserID has the fully qualified user name, i.e. MYDOMAIN\MyUserName

HTH...

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

get the windows user from SQL server

Hello,
I have an application that use SQL server, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks Diego
Look at BOL, in the Functions:
CURRENT_USER
SYSTEM_USER
USER
USER_NAME
(SUSER_NAME)
HTH, Jens Smeyer
http://www.sqlserver2005.de
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:eryji3LQFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have an application that use SQL server, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>
|||these function return the sql users
i am thinking that it isn't possible.

"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eic3p7LQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> Look at BOL, in the Functions:
> CURRENT_USER
> SYSTEM_USER
> USER
> USER_NAME
> (SUSER_NAME)
> HTH, Jens Smeyer
> --
> http://www.sqlserver2005.de
> --
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:eryji3LQFHA.2972@.TK2MSFTNGP14.phx.gbl...
>
|||Now i understand ;-)
No dont think thats possible
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> these function return the sql users
> i am thinking that it isn't possible.
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:eic3p7LQFHA.3196@.TK2MSFTNGP12.phx.gbl...
>
|||Hi
If you are using SQL Authentication, SQL Server does not know the NT user
name as it is not presented by the ODBC/OLE DB stack.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KIdMmMQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Now i understand ;-)
> No dont think thats possible
> Jens.
>
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
>
|||I have found something related with the envirnment variables, but is needed
have admin rights, and on my application there are any users wihtout this
rights.
(if somebody need this way, please send me a mail ask it
pecharroman2001@.hotmail.com)
so i will search another ways.
Thanks Jens!
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KIdMmMQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Now i understand ;-)
> No dont think thats possible
> Jens.
>
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
>
|||Short of sticking with Windows Authentication, I don't think this is
possible. You can get the host name from master..sysprocesses or by
using host_name().
David Gugick
Imceda Software
www.imceda.com
|||"Petxa" wrote:

> I have found something related with the envirnment variables, but is
> needed have admin rights, and on my application there are any users
> wihtout this rights.
> (if somebody need this way, please send me a mail ask it
> pecharroman2001@.hotmail.com)
> so i will search another ways.
A terrible, horrible, really bad hack that might work for you if...
- You have control over your app
- You don't care what app_name() returns
You could set the user name in the connection string on the application
side. See the Win32 API function GetUserName and add the name/value combo
"Application Name=what you got from GetUserName" to your connection string.
You might also want to add some kind of identifying prefix and/or postfix to
the name so that you can check to make sure the value you get from app_name
was set with this technique. You can also manually include the application
name so that connections you view with EM or sp_who are easier to identify.
Then in T-SQL, you can get the current connection's user name with something
like
declare @.uid varchar(50)
set @.uid = app_name()
Craig
|||It is realy a good idea, i haven't still tested but it looks great!
Thank Craig! (and all for your help)
"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message
news:qCB7e.579079$w62.560396@.bgtnsc05-news.ops.worldnet.att.net...
> "Petxa" wrote:
>
> A terrible, horrible, really bad hack that might work for you if...
> - You have control over your app
> - You don't care what app_name() returns
> You could set the user name in the connection string on the application
> side. See the Win32 API function GetUserName and add the name/value combo
> "Application Name=what you got from GetUserName" to your connection
string.
> You might also want to add some kind of identifying prefix and/or postfix
to
> the name so that you can check to make sure the value you get from
app_name
> was set with this technique. You can also manually include the
application
> name so that connections you view with EM or sp_who are easier to
identify.
> Then in T-SQL, you can get the current connection's user name with
something
> like
> declare @.uid varchar(50)
> set @.uid = app_name()
> Craig
>

Get the windows user from SQL

Hello,
I have an application that use SQL server 2000, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks DiegoAnswered in .sqlserver
Please dont do double posts.
http://livinginternet.com/i/ia_nq_info_news.htm
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:u$%23QN4LQFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have an application that use SQL server 2000, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>

Get the windows user from SQL

Hello,
I have an application that use SQL server 2000, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks Diego
Answered in .sqlserver
Please dont do double posts.
http://livinginternet.com/i/ia_nq_info_news.htm
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:u$%23QN4LQFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have an application that use SQL server 2000, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>

Get the User Name for a login user

Hi Guys,

How can I get the current loged in UserName in windows in MSSQL?

The problem is:

Users login into their PC (Windows Login)Enter a URL of the intranet applicationThey would then be able to access the web base application (Intranet)The web application has a string connection to make a connection to the databaseI want to have a Trigger for my "products" table so if the table is modified (Insert/Update) all those changes be entered in "products_Audit" tableNow on "products_Audit" table I have a field "ChangedBy" where I want to store the windows loged in User Name (SYSTEM_USER just return the Username for the SQL connection that the web application use)

I would really appreciate all your help and suggestions.

Regards,
Mehdi

Hello,

In case of IntraNet where directory security does not allow anonymous users, then you can retrieve domain user information.

dim user as string = Request.ServerVariables("LOGON_USER")

and pass this variable for your database action query.