locked
On permises Connection issues RRS feed

  • Question

  • Hi Guys,

    I get below error,

    Port is open able to remote, service account have admin rights and access to database; Able to connect through remote from other PC as well, TCP Ip/Named Pipe enabled as well.

    Normal SSMS login works fine but fails from ADF...

    An error occurred in Stored Procedure Activity execution. Diagnostic details: Copy activity encountered a user error: ErrorCode=UserErrorFailedToConnectToSqlServer,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Server: xyz.corp.xyz.com,1433, Database: ''Test'', User: 'tech\Test'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Cannot open database "'Test'" requested by the login. The login failed. Login failed for user tech\Test'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=4060,Class=11,ErrorCode=-2146232060,State=1,Errors=[{Class=11,Number=4060,State=1,Message=Cannot open database "'Test'" requested by the login. The login failed.,},{Class=14,Number=18456,State=1,Message=Login failed for user tech\Test'.,},],'.

    Gateway is online, Able to test connection even from there...

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com


    • Edited by Navind Friday, October 14, 2016 7:30 PM
    Friday, October 14, 2016 7:20 PM

Answers

  • Removing single quotes for database name, worked fine; thank you Charles.

    Navin.D http://dnavin.wordpress.com

    • Marked as answer by Navind Friday, December 2, 2016 12:55 AM
    Friday, December 2, 2016 12:55 AM

All replies

  • Hi, Navind

    Could you share the RunId to me? I can do further investigation on it.

    Thanks,

    Charles

    Tuesday, October 18, 2016 3:14 AM
  • ef56ffe6-9047-4e8d-ab24-dd1e9f486a56_636121404000000000_636122268000000000

    Navin.D http://dnavin.wordpress.com

    Wednesday, October 19, 2016 6:49 PM
  • Thank you Navin for sharing the run Id.

    Seems except login failure, no other useful message on my side. I'd like to double confirm with you that you have can test connection successfully from Gateway "Diagnostics" page, is it right?

    Thanks,

    Charles

    Monday, October 24, 2016 9:32 AM
  • Hi Charles,

    We does have copy activity task using same connection and working fine; its only for stored procedure we have the connection issue to the database.

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com

    Monday, October 24, 2016 4:36 PM
  • Thank you for you information. If copy can work, I think we should have some issue there. Because I cannot repro on my side, I may need your help to provide the server log for login failure.

    Could you help to run the below command?
    EXEC sp_readerrorlog 0, 1, 'Login failed' 

    This command will tell us error reason. For example: Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

    Btw, does user password contains some special chars? I'm trying to verify whether we have issue on handle special char, but if you can confirm, it will be helpful.


    • Edited by Charles Gu Tuesday, October 25, 2016 9:21 AM
    Tuesday, October 25, 2016 9:20 AM
  • Btw, for 4060 error, I saw someone maintained it indicates "Missing database"

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d1a06715-fbc8-4361-be51-a817ecf7aea5/sqlclient-connection-error-4060?forum=sqldataaccess

    However, under this case, copy activity should also be failed. I'm not sure whether is caused by some permission setting or others. A possible solution here is try another user with SQL Authentication to see whether it can work.

    Tuesday, October 25, 2016 9:50 AM
  • Hi Charles,

    Login failed for user 'xyz\Login'. Reason: Failed to open the explicitly specified database ''Test''. [CLIENT: 10.161.38.26]
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.161.38.26]

    But same user is sysadmin on the on-premises server; does have access to database.

    Yes password does have lot of special characters..

    Is it an double hop issue, if so my copy activity should not work; else the way connections are considered is different in both activities?

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com


    • Edited by Navind Tuesday, October 25, 2016 4:12 PM
    Tuesday, October 25, 2016 4:09 PM
  • If it is caused by special char, the error message should be password mismatch. So I think it caused by other reason.Actually, for connecting database, the code are almost shared all. So this is little strange for me.

    I notice the one login failure account is "'NT AUTHORITY\ANONYMOUS LOGON'"

    Quick search and The solution is: Give Rights as DB_Owner to " NT AUTHORITY\ANONYMOUS LOGON " on the server as well as on the specific database you are using.

    https://forums.asp.net/t/1924544.aspx?Could+not+find+a+login+matching+the+name+provided

    But I'm little confuse why it happen. Few questions below:

    1. Login failed for user 'xyz\Login' & Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' happen at same time.  is it right?
    2. Could you share me your linked service? You can fake the value for server name and password there.
    3. You server is NOT set to single user mode, is it right?

    Your answer can help us find root cause and improve our quality. Thank you very much~

     
    Wednesday, October 26, 2016 3:05 AM
  • Removing single quotes for database name, worked fine; thank you Charles.

    Navin.D http://dnavin.wordpress.com

    • Marked as answer by Navind Friday, December 2, 2016 12:55 AM
    Friday, December 2, 2016 12:55 AM