locked
Excel strange issue RRS feed

  • Question

  • I have been researching this issue for weeks now and have not found any solutions posted that seem to help my issue.

    We have sql 2005 sp3 installed on windows 2003 r2.  It is set to use mixed authentication.  Our users need to connect to a database on this server using Excel 2007.  If i'm logged into the server, launch excel and go through the steps to connect to a sql datasource leaving the windows authentication radio button checked, it goes through.  If I chose to use a different login radio button, but still type in the same user credentials (using the domain) it fails.

    Remote installations of excel also fail.  I have checked to be sure the users have the ability to login to the database.  The account I am testing with is the dbo and has full access to everything and cannot login with excel from a remote machine.  I've gone through the configuration tools to make sure security was set as instructed and stil no go.

    I have tried removing the sql authentication and using windows only since everything is set to a domain account and still no luck.  It seems to be reporting state 6, but every troubleshooting technique I have seen posted I've tried to no avail.  I've also seen in the log file listed that it is not ready for new connections.  Rebooting the server does not clear it up for me even for a small amount of time not has any of the posts for this particular error. 

    If anyone can help me please, I'd be most appreciative. 

    Monday, April 12, 2010 2:10 PM

Answers

  •  One last try - can you try to create a new system DSN in the database server with the same name as your SQL server ( using sql logins) and try to open your excel and connect again.


    Thanks, Leks
    Tuesday, April 13, 2010 10:18 PM

All replies

  • You cannot type in domain accounts / windows accounts and passwords in place of SQL accounts.If you want to have different domain / windows user to connect to the database then you have to open the excel with -- run as -- option. State 6 is nothing but when a windows user provides his credentials in place of a SQL account.
    See this article on different states where state 6 is also explained http://www.sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456 .

    For remote connections make sure the firewall is not blocking the connections.


    Thanks, Leks
    Monday, April 12, 2010 9:31 PM
  • Thank you for replying!!!  I completely understand what you are saying.  I do have the firewall off completely because that had also crossed my mind.  The issue I seem to running into is even when the user is logged into their client machine as the domain account with access, then launches excel, it still gives them access denied.  They are however able to launch the management studio and work with the database. 

    It just seems to be excel giving the issue and it does seem to be some sort of permission problem which seems weird as the account I am testing with was the one I installed sql with and have setup with full server and db permissions.  I've also made sure that the accounts are not checked to enforce password policy.

    I also notice state 11 and reading the link you provided it states: 

    STATE 11 &12:

    This state means the domain login trying to access sql server that wasn’t explicitly added to sql server .Even though it can access OS, it can’t login in to sql server. To overcome this error, you can add that domain login to sql logins explicitly provided you have create login permission.

    but I have explicitely added my testing user to the sql server with the master db being its default.  I'm sure there is something very small and easily over looked that I am missing which is why this is so frustrating.

     

    Tuesday, April 13, 2010 1:58 PM
  •  One last try - can you try to create a new system DSN in the database server with the same name as your SQL server ( using sql logins) and try to open your excel and connect again.


    Thanks, Leks
    Tuesday, April 13, 2010 10:18 PM