Edit: Multi company sql security issue

gpayne · December 10, 2024, 8:00pm

I am having a security issue with multi company baqs and sql. In the non owning company I am getting an error connecting to sql. I have scoured this site and EpicCare without finding an answer.

The baq works in my company and works for me in the new company, but fails for users that only have access to that company. I know it is company related because if I give a user from the non owning company my company the baqs work.

Any ideas on what setup I missed?

Thanks

Greg

From app server event log:
The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=15355; handshake=6; —>

Also this.
Ice.Common.EpicorServerException: The type initializer for ‘Ice.EulkLicense.LicenseCacheBase1' threw an exception. ---> System.TypeInitializationException: The type initializer for 'Ice.EulkLicense.LicenseCacheBase1’ threw an exception. —> Ice.Common.EpicorServerException: Unable to determine license source. Check LicenseSource appsetting in web.config —> System.Data.Entity.Core.EntityException: The underlying provider failed on Open. —> System.Data.SqlClient.SqlException: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=15355; handshake=6; —> System.ComponentModel.Win32Exception: The wait operation timed out
— End of inner exception stack trace —
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at Epicor.Data.Provider.EpiConnection.Open() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.System\Data\EpiProvider2\EpiConnection.cs:line 229 at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action2 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.Core.EntityClient.EntityConnection.Open() --- End of inner exception stack trace --- at System.Data.Entity.Core.EntityClient.EntityConnection.Open() at Ice.Services.ContextFactory.CreateContext() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.System\Services\ContextFactory.cs:line 57 at Ice.Services.ContextFactory.CreateContext[TDataContext]() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.System\Services\ContextFactory.cs:line 39 at Ice.EulkLicense.DatabaseLicenseProvider.InitCache() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\EULKLicense\DatabaseLicenseProvider.cs:line 194 at Ice.EulkLicense.DatabaseLicenseProvider..ctor() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\EULKLicense\DatabaseLicenseProvider.cs:line 26 at Ice.EulkLicense.LicenseCache.CacheInitFactory.CreateProvider() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\EULKLicense\LicenseCache.cs:line 54 --- End of inner exception stack trace --- at Ice.EulkLicense.LicenseCache.CacheInitFactory.CreateProvider() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\EULKLicense\LicenseCache.cs:line 64 at System.Lazy1.CreateValue()
at System.Lazy1.LazyInitValue() at Ice.EulkLicense.LicenseCacheBase1…cctor() in C:_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.EULK\LicenseCacheBase.cs:line 166
— End of inner exception stack trace —
at Epicor.Hosting.Session.IsModuleInstalled(Guid installationID, Guid moduleID) in C:_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\Session.cs:line 1262
at Epicor.Hosting.Session.IsLicenseTypeInstalled(Guid installationID, Guid sessionType) in C:_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\Session.cs:line 1286
at Epicor.Hosting.Session.SetCompany(String companyID) in C:_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\Session.cs:line 252
at Epicor.Hosting.Session…ctor(String companyID, String userID, Guid sessionType) in C:_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\Session.cs:line 63
at Erp.Extensibility.SessionProvider.ErpSessionBuilder.GetSession(String companyID, String userID, Guid sessionType) in C:_releases\ERP\ERP11.1.200.0\Source\Server\Internal\Extensibility\SessionProvider\ErpSessionBuilder.cs:line 21
at Epicor.Hosting.CallContext.CreateSession(Operation op, CallSettings callSettingsHeader) in C:_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\CallContext.cs:line 170
at Epicor.Hosting.CallContext.Create(Operation op) in C:_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\CallContext.cs:line 132
at Ice.Security.AuthorizationManager.CheckAccess(OperationContext operationContext, Message& message) in C:_releases\ICE\ICE4.1.200.10\Source\Server\Hosting\Wcf\Ice.Server.Wcf.Core\Security\AuthorizationManager.cs:line 84
— End of inner exception stack trace —

JMPCONJ · December 10, 2024, 8:13pm

BAQ in browser doesn’t have anything in the help text but here’s a screencap…one or more may need to be checked…we’re one company so I can’t test from here.

danvoss · December 10, 2024, 8:15pm

I have a multi-company installation, though we only use one company now. Make sure that Cross-Company is NOT enabled and that All Companies is enabled. Also, make sure that Shared is enabled.

gpayne · December 10, 2024, 8:30pm

I have shard and all companies on the baq and the dashboards. They work in the other company, but only if I give those users access to my company which I did for Engineering to keep them moving.

We don’t have any shared things other than baqs and dashboards, so I have not set up multi company direct. Is that a possibility?

MikeGross · December 10, 2024, 9:50pm

The error seems like it’s a license timeout. Are these two companies on different app servers with diff licenses (tied together with Service Bus or something)? It’s the only way (I can think of) that kind of error would appear.

Multicompany is not at play here - it’s for moving other things around that are marked global (or I/C trading transactions).

Based on this, I can see that you have “all companies” and “shared” checked. Maybe even “cross company” if it’s a consolidation style report. The fact that they can see it and get an error says that the BAQ is available to them in the non-owning company. So it’s down to the server running the query.

I have had trouble where I’ve named a BAQ the same as another BAQ in a different company that was not marked “all companies”. I might pull and export of the BAQ and then make sure to delete ALL copies of it, from all companies, then bring it in again and check the boxes. Then see what happens.

Or - Can you send me the BAQ? I’ll throw it in my system and see what’s up.

gpayne · December 10, 2024, 10:20pm

@MikeGross Thanks.

this is the baq and the log is from one of the engineers that I added to the owning company. The error when is fails is the company column is not valid.

baq log.txt (808 Bytes)
zRGList.baq (13.5 KB)

Olga · December 10, 2024, 10:43pm

The query is not cross-company, it is one company only and if you have such strange error in only on company, then the issue probably is what Mike describes:

gpayne · December 10, 2024, 11:03pm

it not just one query and I heeded @MikeGross’s advice and only made baqs and bpms in the first company, so there are no bpms or baqs owned by other companies. Oddly some of my newer work is fine, but the one I posted and another are on baqCombos from e9 days and they both fail or they both work. I just added this user to my BAQ editing security group and they both work and if I remove it they both fail. I added another menu security from the owning company and it works again.

I was just looking at my security groups and some have an owning company and some are blank.

Doug.C · December 10, 2024, 11:07pm

Make sure the Security ID either (a) exists in both companies as non global, or (b) exists in one company marked as “All Companies”.

You can have the same (non global) security ID defined in both companies and assign different people/roles to them, or if it doesn’t matter, you can throw all of them into a global security ID.

gpayne · December 11, 2024, 12:49am

@Doug.C We only have one set of security ids since 2012 and they are all all companies.

I am going to redeploy the site after they leave on the west coast to see if that makes any difference.

gpayne · December 11, 2024, 3:38am

Redeploying did not help and it is not just baqs. after I redeployed without any active sessions there are still several errors of Unable to determine License source per minute.

It is test, so I will wait for support. Live seems fine, but has zero data so it may yet have an issue.

MikeGross · December 11, 2024, 3:20pm

Can you redeploy the license file in Admin Console and click the license all modules button. Maybe something in there is messed up.

Imported the query and it’s fine. Super simple. No Sec ID attached. Honestly can’t think of any security restrictions on users viewing Res Groups or Resources (like Territory Sec with Customers and stuff).

And you’ve got no Directives that might be interfering?

Otherwise - I’d open the ticket with support at this point.

Ryan.Kappel · December 11, 2024, 3:57pm

Mark the Cross-Company and All Companies. Then in your BAQ set the criteria for the tables to only look at the company in question.

gpayne · December 11, 2024, 4:00pm

I did a couple of days ago. I am looking at the activity in event viewer and it too regular to be just related to baqs. Even with the task agent off I am getting errors every three seconds.

MikeGross · December 11, 2024, 4:06pm

Recent MS server patch perhaps? Grasping now,…

Olga · December 11, 2024, 4:09pm

Do you see full error stack either in event viewer or server log?

gpayne · December 11, 2024, 4:31pm

it is in the event viewer only. nothingin the server log. I did turn off most of the logging and bpms seems to be functioning normally.

Here is the trace.

Ice.Common.EpicorServerException: The type initializer for 'Ice.EulkLicense.LicenseCacheBase`1' threw an exception. ---> System.TypeInitializationException: The type initializer for 'Ice.EulkLicense.LicenseCacheBase`1' threw an exception. ---> Ice.Common.EpicorServerException: Unable to determine license source. Check LicenseSource appsetting in web.config ---> System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=15355; handshake=6;  ---> System.ComponentModel.Win32Exception: The wait operation timed out
   --- End of inner exception stack trace ---
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Epicor.Data.Provider.EpiConnection.Open() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.System\Data\EpiProvider2\EpiConnection.cs:line 229
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
   at Ice.Services.ContextFactory.CreateContext() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.System\Services\ContextFactory.cs:line 57
   at Ice.Services.ContextFactory.CreateContext[TDataContext]() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.System\Services\ContextFactory.cs:line 39
   at Ice.EulkLicense.DatabaseLicenseProvider.InitCache() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\EULKLicense\DatabaseLicenseProvider.cs:line 194
   at Ice.EulkLicense.DatabaseLicenseProvider..ctor() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\EULKLicense\DatabaseLicenseProvider.cs:line 26
   at Ice.EulkLicense.LicenseCache.CacheInitFactory.CreateProvider() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\EULKLicense\LicenseCache.cs:line 54
   --- End of inner exception stack trace ---
   at Ice.EulkLicense.LicenseCache.CacheInitFactory.CreateProvider() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\EULKLicense\LicenseCache.cs:line 64
   at System.Lazy`1.CreateValue()
   at System.Lazy`1.LazyInitValue()
   at Ice.EulkLicense.LicenseCacheBase`1..cctor() in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.EULK\LicenseCacheBase.cs:line 166
   --- End of inner exception stack trace ---
   at Epicor.Hosting.Session.IsModuleInstalled(Guid installationID, Guid moduleID) in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\Session.cs:line 1262
   at Epicor.Hosting.Session.IsLicenseTypeInstalled(Guid installationID, Guid sessionType) in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\Session.cs:line 1286
   at Epicor.Hosting.Session.SetCompany(String companyID) in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\Session.cs:line 252
   at Epicor.Hosting.Session..ctor(String companyID, String userID, Guid sessionType) in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\Session.cs:line 63
   at Erp.Extensibility.SessionProvider.ErpSessionBuilder.GetSession(String companyID, String userID, Guid sessionType) in C:\_releases\ERP\ERP11.1.200.0\Source\Server\Internal\Extensibility\SessionProvider\ErpSessionBuilder.cs:line 21
   at Epicor.Hosting.CallContext.CreateSession(Operation op, CallSettings callSettingsHeader) in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\CallContext.cs:line 170
   at Epicor.Hosting.CallContext.Create(Operation op) in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Framework\Epicor.Ice\Hosting\CallContext.cs:line 132
   at Ice.Security.AuthorizationManager.CheckAccess(OperationContext operationContext, Message& message) in C:\_releases\ICE\ICE4.1.200.10\Source\Server\Hosting\Wcf\Ice.Server.Wcf.Core\Security\AuthorizationManager.cs:line 84
   --- End of inner exception stack trace ---

Olga · December 11, 2024, 4:37pm

It looks like problem with your database connection, maybe SQL server itself.
Do you specify pool size in your connections string?

gpayne · December 11, 2024, 4:40pm

Not that I recall, where would that be located?

Olga · December 11, 2024, 5:19pm

your host,config, something like

<add name=“IceContext” connectionString="metadata=res://Ice.Data.Model/IceContext.csdl|res://Ice.Data.Model/IceContext.ssdl|res://Ice.Data.Model/IceContext.msl;provider=System.Data.SqlClient;provider connection string="Data Source=sqlserver;Initial Catalog=database;Integrated Security=False;User ID=sa;Password=YourPassword;Min Pool Size=100;Max Pool Size=2000;MultipleActiveResultSets=True