none
Annoying SQL Login error Generated from BizTalk Server

    질문

  • Hi Guys

    We have a BizTalk 2009 installation that is currently working flawlessly…With the exception that a SQL login failure is generated on the SQL Server.

    This error has no impact on BizTalk whatsoever… (now you’re asking yourself what the problem is then. J )

    The SQL Admin has to deal with over 551 of these login errors on a daily basis. I have been tasked with resolving the issue.But there is simply no indication why this is happening.

    Also this happens for almost every service account used in BizTalk as well as the computer account :-S

    Below the error (altered of course)

    Login failed for user 'Domain\ComputerAccount$'. Reason: Failed to open the explicitly specified database. [CLIENT: Insert IP here]

    Have any of you encountered and resolved this?

    PS: Install specs

    BizTalk 2009 Enterprise edition

    SQL 2008 Standard Edition (SP3)

    2012년 3월 1일 목요일 오전 10:48

답변

  • There are a few ways to troublshoot login failures, this blog has a good way to do it. I would also recommend you to check the log files on the database server. Please provide any findings here so we can help you to troubleshoot this issue.

    Best regards

    Tord Glad Nordahl
    Bouvet ASA, Norway
    http://www.BizTalkAdmin.com |@tordeman

    Please indicate ”Mark as Answer” if this post has answered the question.

    2012년 3월 1일 목요일 오전 11:16

모든 응답

  • Hi

    One thing which i could suggest here, Do not use the BizTalk Service account as a Computer Logon account. I never use it and I guess the document also suggest to use different account for logon and BizTalk host.

    HTH

    Naushad

    2012년 3월 1일 목요일 오전 10:54
    중재자
  • Hi,

    Can you run MessageBoxViewer tool to check if there are any issues with configuration.

    I would suggest to check couple of points

    1) Please confirm that the error which is occuring in SQL server eventviewer is because of BizTalk serevr itself.As the error is very generic it could be because of any other application as well which is accessing some other database on the same SQL server.

    2) Just a guess, Have your BizTalk SQL Server Agent jobs been changed from default ones. In case the user under which these jobs are configured may not have required permission. Also please ensure that all the jobs are running successfully.


    Thanks With Regards,
    Shailesh Kawade
    MCTS BizTalk Server
    Please Mark This As Answer If This Helps You.
    http://shaileshbiztalk.blogspot.com/


    2012년 3월 1일 목요일 오전 10:59
  • Thanks Naushad

    We
    do not use the Service account to login. We have a service account for each function
    of BizTalk as well as a admin user used for install and administrative purposes.


    Also thanks  Shailesh J

    I can confirm that BizTalk
    is the only application on that server. But there are some applications within BizTalk
    that connects back to SQL to a database dedicted to that application.
    The authentication there happens at port level. 

    The BizTalk Jobs are default and configured (else we have no
    support from MS
    J ) and the account
    used to configure these jobs is the domain SQL account and has all the
    nessacery premissions.

    The  weird part is that the SQL error states the machine account as trying to log into SQL.

    Thanks
    for your feedback.


    2012년 3월 1일 목요일 오전 11:12
  • Did you ran MessageBoxViewer tool ?

    Also just make a note that, Not all biztalk jobs are configured by default. Backup, DTA purge etc jobs need manual configuration (these jobs are just created during biztalk configuration).


    Thanks With Regards,
    Shailesh Kawade
    MCTS BizTalk Server
    Please Mark This As Answer If This Helps You.
    http://shaileshbiztalk.blogspot.com/



    2012년 3월 1일 목요일 오전 11:14
  • There are a few ways to troublshoot login failures, this blog has a good way to do it. I would also recommend you to check the log files on the database server. Please provide any findings here so we can help you to troubleshoot this issue.

    Best regards

    Tord Glad Nordahl
    Bouvet ASA, Norway
    http://www.BizTalkAdmin.com |@tordeman

    Please indicate ”Mark as Answer” if this post has answered the question.

    2012년 3월 1일 목요일 오전 11:16
  • Hi,

    Thanks for the details!

    I would suggest you to use "Activity Monitor" and SQL Server Profiler, to further investigate the issue, However I have found few threads here and here which might give you some help while resolving the issue.

    I hope that helps you!

    HTH,Naushad (MCC/MCTS) http://alamnaushad.wordpress.com,My New Technet Wiki Article “BizTalk Server: Performance Tuning & Optimization"

    If this is helpful or answers your question - please mark accordingly! Please "Vote As Helpful" if this was useful while resolving your question!

    2012년 3월 4일 일요일 오후 1:19
    중재자
  • Hi,

    I would suggest use the following post to identify what component/application is causing this issue.

    HTH

    Steef-Jan Wiggers

    MVP & MCTS BizTalk Server 2010

    http://soa-thoughts.blogspot.com/ | @SteefJan

    If this answers your question please mark it accordingly


    BizTalk

    2012년 3월 5일 월요일 오전 4:34
    중재자
  • add user 'Domain\ComputerAccount$' to your sql server. I also had this issue some time back. i was having this issue on both biztalk 2006 and 2010 machines. if you having 4 or 6 server's you might be getting this error for all the servers.

    after adding this user's to sql server all errors gone for me. you can also try this.

    thanks,

    Amit More

    If this is helpful or answers your question - please mark accordingly! Please "Vote As Helpful" if this was useful while resolving your question!

    2012년 3월 5일 월요일 오전 6:20