Disabling Service Shell Security

SQL trick with JOIN vs APPLY (JOINING to get the top 1)

So, assume this:

select * from meddenials

DenialId DenialTypeId DenialCode DenialDescrip
1                       1 1420         Not Medically Necessary
2                       1 1500         Other transportation available
3                       1 1600         No Eligibility Coverage on date of service
4                       2 100                 Missing Signature
5                       2 200                 Incomplete Information
6                       2 300                 Mileage over estimate threshold

And


select * from meddenialtypes



DenialTypeId DenialTypeDescrip
1                 Trip Denial
2                 Claim Denial


And

select * from medreimbursementdenials where bookingid = 44479


BookingId DenialTypeId DenialId
44479                       1 1
44479                       2 5


So, for an Encounter file, we wanted to get the top denial reason, so I tried something like this:



SELECT DenialReason.*,md.*
FROM Booking b
LEFT OUTER JOIN ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials where bookingid = b.bookingid )
AS DenialReason
LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId
where b.bookingid = 44479

but that causes an error:

Msg 156, Level 15, State 1, Line 6

Incorrect syntax near the keyword 'where'.

So I tried moving the where clause out of the "sub table":

SELECT DenialReason.*,md.* 

FROM Booking b

LEFT OUTER JOIN ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials  ) 

AS DenialReason on DenialReason.BookingId = b.BookingId

LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId 

where b.bookingid = 44479

Which is ok syntactically, but doesn't return the results the way we want:

DenialId BookingId DenialId DenialTypeId DenialCode DenialDescrip

NULL NULL NULL NULL NULL NULL

So, I googled around and found this page:

http://stackoverflow.com/questions/2033699/top-1-with-a-left-join

which introduced a key word I wasn't familiar with "OUTER APPLY"

SELECT DenialReason.*,md.* 

FROM Booking b

OUTER APPLY ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials where bookingid = b.bookingid ) 

AS DenialReason 

LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId 

where b.bookingid = 44479

does the trick:

DenialId BookingId DenialId DenialTypeId DenialCode DenialDescrip

1               44479 1                       1 1420 Not Medically Necessary

Troubleshooting MT Portal -> Sched Server communication

To Troubleshoot Portal -> Portal Web Service -> Service Shell -> Sched Server communication.

Start the service shell and browse to

http://localhost:<portNumber>/context

Examine these properties (there are example values provided):

PASS/PassInfoServer/SchedServ/Config/IP AZ-DEV4
PASS/PassInfoServer/SchedServ/Config/Port 11002


PASS/Trapeze4/Schedule Server/Host Address AZ-DEV4
PASS/Trapeze4/Schedule Server/Host Port 11002

NOTE: these are stored in the CorePropValues


Profile/Network/PrimaryServerHost localhost
Profile/Network/PrimaryServerPort (port of Service Shell) 8081

So, we had a working configuration here of one of our customer sites running locally.  I saved off the PropValues table of that working configuration in another DB- got a fresh copy of the data from the customer site and restored locally- and then brought back the original PropValues table thinking that I'd be able to turn it on and go.  I found that there must be some config values not stored in the PropValues table. Not sure where they are stored.  But prior to restoring take a look at these values!

//doesn't come in through PropValues

PASS/PassInfoServer/SchedServ/Config/IP
PASS/Trapeze4/Schedule Server/Host Address

PASS/Trapeze4/Schedule Server/Host Port


Also, check this as well:
<driveLetter>:\Data\tpnet.ini

ALSO: if the portal has an error message saying that it can't connect to the service shell, double check the trace switches on the shell, Core -> Security -> and verify that authentication is turned off.

MS SQL Restore failed ...Exclusive access could not be obtained

So, sometimes you'll encounter this when you're trying to restore a SQL DB.  Usually killing the associated schedule server and service shell resolves this issue.  But you may have a query window associated with the DB or some other process that isn't apparent.  In this case, you can open up a query window for the "master" DB, and type:


sp_who

and then use the kill command with the process id

kill 51

Trapeze Pass: Troubleshooting DB connectivity between the Trapeze Service Shell and the Schedule Server

So, recently came across an unreported connectivity problem between the MT portal and the Schedule Server.  It seems that the ping from MT does not necessarily test DB connectivity.  So at least in V1 of the MT Portal, a successful ping does not mean that a scheduling call to the schedule server will succeed.  So, the MT homepage indicates that there was a successful load, but if you actually try to schedule, you'll get an error in the sched server log without the error being returned to MT. Here's a snippet of the error from the schedule server.  Note: a certain level of trace will have to be turned on:



<Message tcftype="10">ConnectionPool::GetConnection - failed to connect to datasource

SQLConnect returned SQL_ERROR (from HDBC), SqlState=28000, Native Error=18456, SQL_ERROR=0
Message=[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user &#39;trapeze&#39;.</Message>

So, I'm pretty sure the above login failed message has to do with the user name password configured within the Service Shell as seen below (Shell menu item, then Configuration), as opposed to the ODBC.

That said, it seems that the shell uses both the configured value (as above) as well as the SQL account configured within the ODBC.

MT Portal Security

Ok, questions recently came up about why the MT Portal virtual directory in IIS was set to "Allow Anonymous" access.  After doing a little research, here's the email response I came up with.


MT uses forms authentication, and therefore does not rely on IIS authentication. This is why IIS is set to allow Anonymous Access; it is an accepted practice.

http://msdn.microsoft.com/en-us/library/ff647070.aspx

I found some other links that are worth checking out:

http://msdn.microsoft.com/en-us/library/ee825205(v=cs.10).aspx

http://msdn.microsoft.com/en-us/library/yedba920(v=vs.100).aspx

http://msdn.microsoft.com/en-us/library/7t6b43z4(v=vs.100).aspx

Integrating ASP.NET Authentication with IIS

In addition to relying on the authentication capabilities of IIS, you can perform authentication in ASP.NET. When considering ASP.NET authentication, you should understand the interaction with IIS authentication services.IIS assumes that a set of credentials maps to a Microsoft Windows NT account and that it should use thosecredentials to authenticate a user. The authentication methods used in IIS 7 are the following: anonymous,ASP.NET impersonation, basic, client certificate mapping, digest, forms, and Windows IntegratedSecurity (NTLM or Kerberos). You can select the type of authentication by using IIS administrative services.

For information, see Configuring Authentication in IIS 7.

If users request a URL that maps to an ASP.NET application, the request and authentication information
are handed off to the application. ASP.NET provides forms authentication. Forms authentication is a system
by which unauthenticated requests are redirected to an ASP.NET Web page that you create.
The user provides credentials and submits the page. If your application authenticates the request,
the system issues an authentication ticket in a cookie that contains the credentials or a
key for reacquiring the identity. Subsequent requests include an authentication ticket with the request.
NOTE: ASP.NET membership and ASP.NET login controls implicitly work with forms authentication.

Trapeze Pass: Connect failed No connection could be made because the target machine actively refused it.

Ok, troubleshooting Trapeze server (service) shell connectivity to the Trapeze Schedule server, I encountered this error- specifically, when the MT portal login page attempts to ping the sched server, you would see the following in the service shell log:

   ==>ERROR at [2012/09/25, 8:17:45:254] cls:, obj::
Caught SocketException in ('T4Client::Call', C:\Trapeze\Core1\TCL\CTpNet.CPP, line=498):
Message=MessageSocket::Connect failed [Service=, Host=10.22.1.58, Port=11001]
SocketError=10061. No connection could be made because the target machine actively refused it.
Cls: SocketException
--Stack Trace--
SrvShell/Dispatch/Scheduling.SchLoad

First make sure the sched server is running. (I believe you'll get the "No connection could be made because the target machine actively refused it." error when the sched server is not running)

Also, note, this is from the version 1 of the portal, when the ping was a bastardized call to load a schedule (sch id = 0).

The things that helped resolve this had to do with the TpNet.ini file associated with the data folder of the Sched Server and work station installation.  In this case the service shell was located on a different machine than the sched server.  However the sched server has to be configured to accept socket connections, and that configuration takes place in the TpNet.ini file.  For example:


# TPNET_ADDRESS    MACHINE_NAME    ADDRESS    PORT    PROTOCOL
#
# Example:
# SCHSRV.DEV1    Toronto_dev_1    192.5.10.4    11001    tcp

Also, I'm not sure how the call stack works, but I've seen this error when the port configuration for the PassInfoServer is out of synch with the TpNet.ini file.  (They should have the same value and be in synch with the services file)


PASS/PassInfoServer/SchedServ/Config/IP  AZ-DEV4
PASS/PassInfoServer/SchedServ/Config/Port  11002

p

In the case, where the IP is an IP address vs. a name, use ipconfig /all to confirm the IP of the machine of the sched server!

access the property through the service shell property page:
http://localhost:8082/context

Also have to have the tcp/udp entries in the services file usually located here:
C:\windows\system32\driver\etc\services

here are some example entries.

SCHSRV.TEST 11001/tcp
SCHSRV.TEST 11001/udp

And check the strvalue of the PropValues to ensure that the configuration is correct for the path of the TpNet.ini file. You can examine the full path it by running this query:

select * from propvalues where strvalue like '%TpNet.ini%'
 

Invalid URI: The format of the URI could not be determined

So, here's another one I was encountered with: Invalid URI: The format of the URI could not be determined

Its a 2003 server running IIS 6.0

Event code: 3005 

Event message: An unhandled exception has occurred. 

Event time: 9/13/2012 1:38:13 PM 

Event time (UTC): 9/13/2012 5:38:13 PM 

Event ID: 00f54b7251484446b49b7d508a57a23b 

Event sequence: 3 

Event occurrence: 1 

Event detail code: 0 

Application information: 

Application domain: /LM/W3SVC/1/Root/PGC-1-129920314485468750 

Trust level: Full 

Application Virtual Path: /PGC 

Application Path: C:\Inetpub\wwwroot\PGC\ 

Machine name: SYSENG2 

Process information: 

Process ID: 1136 

Process name: w3wp.exe 

Account name: NT AUTHORITY\NETWORK SERVICE 

Exception information: 

Exception type: UriFormatException 

Exception message: Invalid URI: The format of the URI could not be determined. 

Request information: 

Request URL: http://syseng2/pgc/DesktopDefault.aspx 

Request path: /pgc/DesktopDefault.aspx 

User host address: 127.0.0.1 

User: 

Is authenticated: False 

Authentication Type: 

Thread account name: NT AUTHORITY\NETWORK SERVICE 

Thread information: 

Thread ID: 1 

Thread account name: NT AUTHORITY\NETWORK SERVICE 

Is impersonating: False 

Stack trace: at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)

at System.Uri..ctor(String uriString)

at System.Web.Services.Protocols.WebClientProtocol.set_Url(String value)

at ASPNET.StarterKit.Portal.TrapezeService.TrapezeMTSrv.set_Url(String value)

at ASPNET.StarterKit.Portal.TrapezeService.TrapezeMTSrv..ctor()

at ASPNET.StarterKit.Portal.Signin.Page_Init(Object sender, EventArgs e)

at System.Web.UI.Control.OnInit(EventArgs e)

at System.Web.UI.UserControl.OnInit(EventArgs e)

at System.Web.UI.Control.InitRecursive(Control namingContainer)

at System.Web.UI.Control.AddedControl(Control control, Int32 index)

at System.Web.UI.ControlCollection.Add(Control child)

at ASPNET.StarterKit.Portal.DesktopDefault.Page_Load(Object sender, EventArgs e)

at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)

at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)

at System.Web.UI.Control.OnLoad(EventArgs e)

at System.Web.UI.Control.LoadRecursive()

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Custom event details: 

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. 

 So after a fair amount of digging, the problem turned out to be caused by using a default web.config that had stubbed out addresses for web services. See "[stub value]" below. Once this was changed to a syntax correct address (even if the resource is unavailable), the page rendered.


  <applicationSettings>
    <ASPNET.StarterKit.Portal.Properties.Settings>
      <setting name="ASPNETPortal_FaxService_FaxService" serializeAs="String">
        <value>http://localhost/FaxWebService/FaxService.asmx</value>
      </setting>
      <setting name="ASPNETPortal_MSReportingService2005_ReportingService2005" serializeAs="String">
        <value>http://az-dev4/ReportServer/ReportService2005.asmx</value>
      </setting>
      <setting name="ASPNETPortal_MSReportExecution2005_ReportExecutionService" serializeAs="String">
        <value>http://az-dev4/ReportServer/ReportExecution2005.asmx</value>
      </setting>
      <!--setting name="ASPNETPortal_MapPointService_CommonService" serializeAs="String">
        <value>http://findv3.mappoint.net/Find-30/Common.asmx</value>
      </setting-->
      <setting name="ASPNETPortal_MSReportingService_ReportingService" serializeAs="String">
        <value>http://az-dev4/ReportServer/ReportService.asmx</value>
      </setting>
      <setting name="ASPNETPortal_TrapezeService_TrapezeMTSrv" serializeAs="String">
        <value>[stub value]</value>
      </setting>
    </ASPNET.StarterKit.Portal.Properties.Settings>