locked
Issue with Intranet Page Running a Procedure RRS feed

  • Question

  • User1769015664 posted

    The intranet application running a stored procedure (when a hyperlink is clicked) runs fine on my machine but it fails on other users.

    How do I resolve this issue?

    Monday, March 9, 2020 7:39 PM

All replies

  • User475983607 posted

    The intranet application running a stored procedure (when a hyperlink is clicked) runs fine on my machine but it fails on other users.

    How do I resolve this issue?

    Usually there is an error message when a stored producer fails.  What is the error message?  Can you share the code that reproduces this behavior?

    Monday, March 9, 2020 7:41 PM
  • User-1716253493 posted

    Where is the app running, server machine or local machine?

    Tuesday, March 10, 2020 12:47 AM
  • User1769015664 posted

    The app is running on a server and the error is System.Data.SqlClient.SqlException: Login failed for user 'AppServer\MyUser'.
    Running app on my local machine works fine but it gives me that error when I am on the server using the same account as my local account.

    Tuesday, March 10, 2020 1:12 PM
  • User475983607 posted

    The app is running on a server and the error is System.Data.SqlClient.SqlException: Login failed for user 'AppServer\MyUser'.
    Running app on my local machine works fine but it gives me that error when I am on the server using the same account as my local account.

    The error is very clear.  The application is running under "AppServer\MyUser" which does not have a login account in the target SQL server.  I assume the login works on your machine because you are running as an admin.  Also it is not clear if you are logging into the same SQL server.  You'll need to do a little research as we, the community , do not have enough information to provide an accurate solution.

    Tuesday, March 10, 2020 1:26 PM
  • User1769015664 posted

    No, I am not running as an 'Admin' on my local computer, using the same account "AppServer\MyUser" in both environment

    Tuesday, March 10, 2020 1:38 PM
  • User2053451246 posted

    What account is IIS using?  That is the account that will be accessing SQL Server.  Make sure the IIS app pools are using the same identity on both PCs.

    Tuesday, March 10, 2020 2:22 PM
  • User475983607 posted

    No, I am not running as an 'Admin' on my local computer, using the same account "AppServer\MyUser" in both environment

    Are you sure?  By default, web applications on IIS run under the application pool identity not the user's Windows Authentication account.  Does your connection string use Integrated Security or Trusted_Connection?  If so, try setting the application pool identity as stated above.  

    Tuesday, March 10, 2020 2:32 PM
  • User1769015664 posted

    I am a bit confused.

    Whatever the App Pool is, the app works fine on one computer but not on another computer. It doesn't even work if the app is running on the server.

    If the pool is an issue then why does it work on my computer?

    Tuesday, March 10, 2020 2:38 PM
  • User475983607 posted

    Whatever the App Pool is, the app works fine on one computer but not on another computer. It doesn't even work if the app is running on the server.

    If the pool is an issue then why does it work on my computer?

    The common problem in the forum is the login on the developers machine works because the developer is an admin and the application is running under the admin credentials.  the login stops working on another machine because the application is no longer running under the same admin credentials.

    Keep in mind, the community cannot see any of your configurations and you have not clearly explained how your SQL security works.  We can only guess using the most common issue found on these forums.

    Tuesday, March 10, 2020 2:47 PM
  • User1769015664 posted

    That makes sense.

    How do I check if application is running under admin credentials?

    Tuesday, March 10, 2020 3:33 PM
  • User475983607 posted

    The first step is explaining how your SQL security is intended to work.   

    NJ2

    How do I check if application is running under admin credentials?

    Are you running the application locally using IIS Express and Visual Studio?  If so, you are running the application under your account which is generally an admin since you are developer.   Where is the SQL server?  Remote?  Local?

    Does your connection string have Integrated Security=SSPI or Trust Connection=true?   If so, SQL is expecting a Windows account.  Application is hosted remotely run under the Application Pool Identity which is commonly IIS APPPOOL\your.app.name.   It depends on the version of IIS.  Anyway, IIS APPPOOL\your.app.name is local account.  Usually the SQL server is remote and therefore the login will fail.  

    A common solution for Intranet App is to run the Application Pool under a custom account that has Read/Write access to a database.  Then you don't end up with a username and password in the connection string and the account is managed by your security group.

    Another common solution is using SQL login.  The downside is you end up with a username and password in the configuration and source control. 

    There's pass-through authentication which uses the current user's account.  This requires accounts for ever user in SQL and network configuration.

    Tuesday, March 10, 2020 3:55 PM
  • User1769015664 posted

    I am not using a development environment or visual studio on my machine, running it the same way as the other users.

    Tuesday, March 10, 2020 4:01 PM
  • User475983607 posted

    I am not using a development environment or visual studio on my machine, running it the same way as the other users.

    Your response does not clarify how your security works.  Perhaps there is someone on your team that can help you?

    Tuesday, March 10, 2020 4:03 PM
  • User1769015664 posted

    The Event Viewer shows the following error:

    Login failed for user 'MyServer\MyUser'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

    Tuesday, March 10, 2020 6:15 PM
  • User475983607 posted

    That particular error while common usually  means there is something wrong with the account in SQL.  Do you see the MyServer\MyUser account in SQL?  Is the account configured as a login and does the account have proper role membership in the target database?

    You can also use the SQL profiler tool to see if you get a more specific error.  

    Again, you have not provided any concrete information on how your security works.  Maybe you can reach out to a team member for assistance. 

    Tuesday, March 10, 2020 6:36 PM
  • User1769015664 posted

    MyServer\MyUser account works fine on one machine but the same account doesn't work on another machine.

    It is Windows Authentication.

    Tuesday, March 10, 2020 6:46 PM
  • User475983607 posted

    MyServer\MyUser account works fine on one machine but the same account doesn't work on another machine.

    It is Windows Authentication.

    Can you clarify?  Are you seeing a login prompt when accessing the site or are you logging in on a different machine?  Are you always access the same remote URL or are to running the web application from localhost on a dev machine?  Can you explain how the SQL security works?

    Tuesday, March 10, 2020 7:08 PM
  • User-775646050 posted

    Do you have access to the database? In Sqlserver Mangement Studio expand your database and then expand security and users. Is the MyServer\MyUser account listed there?

    Tuesday, March 10, 2020 8:02 PM
  • User1769015664 posted

    You are missing the point...

    The user MyServer\MyUser works on one machine but not on other machine.  I have 2 computers logged in with my AD account (MyServer\JohnDoe), the app on both computers. work fine (app is launched, select a menu option from dropdown, a new page renders data with hyperlinks, I click on these hyperlinks which executes a stored procedure and bring up another page). All is good here.

    Now, I walk over to another machine and log in, perform the same as above and when I click on hyperlinks, I get the error posted above. I then remote into the server and login as my ID MyServer\JohnDoe, perform the same as above and when I click on hyperlinks, I get the error posted above.

    Permissions on stored procedure are fine and it is obvious since could run it from two of my machines.

    Tuesday, March 10, 2020 8:34 PM
  • User475983607 posted

    How is the SQL security configured?   Your description sounds like you expect pass-through security where the web server forwards the user's windows credentials to SQL.  This takes a bit of work to get setup and if you have pass-through security then you or someone on your team surely knows.

    The community has asked a bunch of question and you've answer very little.  So I'm at a loss.  I cannot reproduce this issue. You might want to try setting the Application Pool to your known Identity and see if that works.  You can also fire up SQL Profiler to see SQL login error which might have more information than the event viewer.

    Did you happen to modify the hosts file on you machine to point to a different URL?

    Tuesday, March 10, 2020 10:01 PM