how to connect to DB server through application server ?

julkifli33

hi all
i have scenario like this, we are using Nav 2009 R2 and RTC
we set 2 servers and 10 clients
1 server for database server (installed ms SQL server 2008 R2)
1 server for application server (installed Nav 2009 R2)
and 10 clients, all installed RTC

what i want is.... these 10 clients will connect to application server
and the service in application server will stored data in database server
from application server itself, yes it could connect to database server localhost:7046/DynamicsNav
but when using clients to ApplicationServer:7046/DynamicsNav it cannot
it was said You dont have permission to ApplicationServer SQL Server Login
and then i test installed RTC in database server
i connect from client direct to database server DatabaseServer:7046/DynamicsNav
yes it can !!

do I miss something? please help...
thanks

mohana_cse06

what are the logon accounts in Application server and database server?

Please follow this link
http://msdn.microsoft.com/en-us/library/dd301254.aspx

julkifli33

for application server , if i used local and network service
it can't connect... even from application server itself

but i create another domain login
yes it can connect from application server... but cant from clients

julkifli33

it still the same
after i follow all the steps
when connect from application server itself, i am able to connect
but from client to application server, cannot

mohana_cse06

have you done change listener setup also?

What about event viewer log?

julkifli33

all the steps i already follow
but for this part... is it for database server or application server?

To create service principal names

Open an elevated command prompt. To do this, click Start, and then in the search window, type Command Prompt. Right-click Command Prompt, and then click Run as administrator.

At the command prompt, create an SPN for the Microsoft Dynamics NAV Server service. The syntax is:

setspn -A InstanceName/FullyQualifiedDomainNameOfServer:Port Domain\User
Using NAVSERV, which is the computer running Microsoft Dynamics NAV Server, and DynamicsNAV, which is the default instance name for Microsoft Dynamics NAV Server, the actual command has the following format:

setspn -A DynamicsNAV/NAVSERV.yourDomain.yourCompany.com:7046 yourDomain\yourUser
Replace "yourDomain," "yourCompany," and "yourUser" with the appropriate values.

Create an SPN for the SQL Server service. This service runs on the NAVSQL computer with a default instance name of MSSQLSvc. Type the following command:

setspn -A MSSQLSvc/NAVSQL.yourDomain.yourCompany.com:1433 yourDomain\yourUser
Again, replace "yourDomain," "yourCompany," and "yourUser" with the appropriate values.

mohana_cse06

Read this

Create Service Principal Names

The first step in setting up delegation is to create service principal names (SPN). To make delegation more secure, Active Directory uses Kerberos to authenticate services. An SPN is the name by which a client uniquely identifies an instance of a service, using the account under which the service runs. You must create one SPN for the Microsoft Dynamics NAV Server service and one SPN for the SQL Server service to make delegation work.

BTW, your SQL server and NAV server both are in Domain?

julkifli33

mohana_cse06 wrote:

Read this

Create Service Principal Names

The first step in setting up delegation is to create service principal names (SPN). To make delegation more secure, Active Directory uses Kerberos to authenticate services. An SPN is the name by which a client uniquely identifies an instance of a service, using the account under which the service runs. You must create one SPN for the Microsoft Dynamics NAV Server service and one SPN for the SQL Server service to make delegation work.

BTW, your SQL server and NAV server both are in Domain?

yes in the same domain

for the event viewer

Log Name: Application
Source: MSSQLSERVER
Date: 8/16/2011 2:59:33 PM
Event ID: 18456
Task Category: Logon
Level: Information
Keywords: Classic,Audit Failure
User: ANONYMOUS LOGON
Computer: DatabaseServer.domain.com
Description:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.1.17]
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event"&gt;
<System>
<Provider Name="MSSQLSERVER" />
<EventID Qualifiers="49152">18456</EventID>
<Level>0</Level>
<Task>4</Task>
<Keywords>0x90000000000000</Keywords>
<TimeCreated SystemTime="2011-08-16T06:59:33.000000000Z" />
<EventRecordID>27996</EventRecordID>
<Channel>Application</Channel>
<Computer>DatabaseServer.Domain.com</Computer>
<Security UserID="S-1-5-7" />
</System>
<EventData>
<Data>NT AUTHORITY\ANONYMOUS LOGON</Data>
<Data> Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.</Data>
<Data> [CLIENT: 192.168.1.17]</Data>
<Binary>184800000E0000000D0000005300540041004E004C00450059002D0054003400300030000000070000006D00610073007400650072000000</Binary>
</EventData>
</Event>

how come it use NT AUTHORITY\ANONYMOUS LOGON ??
my guess is because of this
i already set user for my domain i set in service

mohana_cse06

Then Use Best Practice Analyzer Tool to find out where the problem lies.
You can download it from the following partner source login.
You need to install and run it from the Dynamics NAV Server (Middle tier) machine.
https://mbs.microsoft.com/Cms/Templates/document/General.aspx?NRMODE=Published&NRNODEGUID={C2F2C563-6583-4DC5-BFDC-DD057A63E4A9}&NRORIGINALURL=/partnersource/deployment/resources/supplements/BestPracticesAnalyzerNAV2009&NRCACHEHINT=Guest&wa=wsignin1.0


And also have a look at this blog
http://blogs.msdn.com/b/nav_developer/archive/2009/06/12/setting-up-nav-2009-on-three-machines.aspx

mohana_cse06

one more
http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

julkifli33

mohana_cse06 wrote:

Then Use Best Practice Analyzer Tool to find out where the problem lies.
You can download it from the following partner source login.
You need to install and run it from the Dynamics NAV Server (Middle tier) machine.
https://mbs.microsoft.com/Cms/Templates/document/General.aspx?NRMODE=Published&NRNODEGUID={C2F2C563-6583-4DC5-BFDC-DD057A63E4A9}&NRORIGINALURL=/partnersource/deployment/resources/supplements/BestPracticesAnalyzerNAV2009&NRCACHEHINT=Guest&wa=wsignin1.0


And also have a look at this blog
http://blogs.msdn.com/b/nav_developer/archive/2009/06/12/setting-up-nav-2009-on-three-machines.aspx

thanks for the link
and the problem is about delegation

Constrained delegation is not configured for the correct port SPN 'MSSQLSvc/ComputerName:1433' for user account 'MyDomain\MyUser'.

when i use the step --> setspn -A InstanceName/FullyQualifiedDomainNameOfServer:Port Domain\User
it was said insufficient right
(i executed this in database server)

julkifli33

this is the error message in command prompt

Registering ServicePrincipalNames for CN=My user domain name,OU=Users,OU=Domain HQ,DC=domain,DC=com,DC=sg
DynamicsNav/ComputerName.Domain.com.sg:7046
Failed to assign SPN on account 'CN=My user domain name,OU=Users,OU=Domain HQ,DC=domain,DC=com,DC=sg', error 0x2098/8344 -> Insufficient access rights to perform the operation.

mohana_cse06

How did you login to nav server? as a system administrator or not?

julkifli33

mohana_cse06 wrote:

How did you login to nav server? as a system administrator or not?

i login as my own domain login
but already set as administrator

mohana_cse06

logging with administrator rights is different and login as administrator is different

julkifli33

mohana_cse06 wrote:

logging with administrator rights is different and login as administrator is different

but when i execute command prompt,I already follow the instruction
run as administrator

julkifli33

when we set delegation and spn
where do we set it?
is it in NAV Server? domain server? ord DB Server?

rhpnt

julkifli33 wrote:

hi all
i have scenario like this, we are using Nav 2009 R2 and RTC
we set 2 servers and 10 clients
1 server for database server (installed ms SQL server 2008 R2)
1 server for application server (installed Nav 2009 R2)
and 10 clients, all installed RTC

Do you mean "NAV application server" or "NAV server"?

julkifli33 wrote:

what i want is.... these 10 clients will connect to application server
and the service in application server will stored data in database server
from application server itself, yes it could connect to database server localhost:7046/DynamicsNav
but when using clients to ApplicationServer:7046/DynamicsNav it cannot
it was said You dont have permission to ApplicationServer SQL Server Login
and then i test installed RTC in database server
i connect from client direct to database server DatabaseServer:7046/DynamicsNav
yes it can !!

If you ment "NAV server" - Why would you want to bypass the business logic layer which is "by design"?

julkifli33

yes.. i want to user nav server
so it will be 3 tier

julkifli33

rhpnt wrote:

julkifli33 wrote:

hi all
i have scenario like this, we are using Nav 2009 R2 and RTC
we set 2 servers and 10 clients
1 server for database server (installed ms SQL server 2008 R2)
1 server for application server (installed Nav 2009 R2)
and 10 clients, all installed RTC

Do you mean "NAV application server" or "NAV server"?

julkifli33 wrote:

what i want is.... these 10 clients will connect to application server
and the service in application server will stored data in database server
from application server itself, yes it could connect to database server localhost:7046/DynamicsNav
but when using clients to ApplicationServer:7046/DynamicsNav it cannot
it was said You dont have permission to ApplicationServer SQL Server Login
and then i test installed RTC in database server
i connect from client direct to database server DatabaseServer:7046/DynamicsNav
yes it can !!

If you ment "NAV server" - Why would you want to bypass the business logic layer which is "by design"?

what do you mean bypass business logic layer?

rhpnt

You wrote:

julkifli33 wrote:

...and then i test installed RTC in database server
i connect from client direct to database server DatabaseServer:7046/DynamicsNav
yes it can !!

so I figured you are trying to bypass the middle tier (to connect directly to the db usually means 2 tiers). Anyway, then you wrote:

julkifli33 wrote:

when connect from application server itself, i am able to connect
but from client to application server, cannot

the question that arises here is: "How and to what are you connecting to?"

Then you wrote:

julkifli33 wrote:

...but when using clients to ApplicationServer:7046/DynamicsNav it cannot
it was said You dont have permission to ApplicationServer SQL Server Login

That would simply mean that your clients (PC's) don't have access to the middle tier (NAV) server. Either the server is not in the (right) domain or the clients (PC's) have not been configured to have access to the server.

julkifli33

rhpnt wrote:

You wrote:

julkifli33 wrote:

...and then i test installed RTC in database server
i connect from client direct to database server DatabaseServer:7046/DynamicsNav
yes it can !!

so I figured you are trying to bypass the middle tier (to connect directly to the db usually means 2 tiers). Anyway, then you wrote:

julkifli33 wrote:

when connect from application server itself, i am able to connect
but from client to application server, cannot

the question that arises here is: "How and to what are you connecting to?"

Then you wrote:

julkifli33 wrote:

...but when using clients to ApplicationServer:7046/DynamicsNav it cannot
it was said You dont have permission to ApplicationServer SQL Server Login

That would simply mean that your clients (PC's) don't have access to the middle tier (NAV) server. Either the server is not in the (right) domain or the clients (PC's) have not been configured to have access to the server.

is it because the service rights?
because using classic... using client it can connect to apps server or db server

julkifli33

i already used Best practice analyzer
it seems to be okay....
i already create SPN
i already delegate and using kerberos

but when i check in the event viewer in db server... why it still said login failed for user NT AUTHORITY\ANONYMOUS LOGON ??
reason : Token-based server access validation failed with an infrastructure error

rhpnt

julkifli33 wrote:

is it because the service rights?
because using classic... using client it can connect to apps server or db server

The classic client has nothing to do with the NAV server.

julkifli33 wrote:

i already used Best practice analyzer
it seems to be okay....
i already create SPN
i already delegate and using kerberos

but when i check in the event viewer in db server... why it still said login failed for user NT AUTHORITY\ANONYMOUS LOGON ??
reason : Token-based server access validation failed with an infrastructure error

As the error says, you have a infrastructure/system problem not a NAV problem. The system administrator has to set up the apropriate users and rights in the domain and servers which in turn you assign in NAV.

julkifli33

rhpnt wrote:

julkifli33 wrote:

is it because the service rights?
because using classic... using client it can connect to apps server or db server

The classic client has nothing to do with the NAV server.

julkifli33 wrote:

i already used Best practice analyzer
it seems to be okay....
i already create SPN
i already delegate and using kerberos

but when i check in the event viewer in db server... why it still said login failed for user NT AUTHORITY\ANONYMOUS LOGON ??
reason : Token-based server access validation failed with an infrastructure error

As the error says, you have a infrastructure/system problem not a NAV problem. The system administrator has to set up the apropriate users and rights in the domain and servers which in turn you assign in NAV.

is it in SQL User?
or my service domain account?
i already check using best practice analyszer... all set
done
but still cannot login