none
Login failed for user "..." SQL vs Windows authentication.

    Question

  • I have spend 3 days reading numerous posts to find the answer to the above. Still have not done so. Any answers for me please? The following applies:

    SQL Server 2008 Enterprise edition.
    Connection code from vb6 sp6 =     MyConnObj.Open "Provider = SQLNCLI10;Data Source=OLDPC\ISRSDMS,1433;Network Library=DBMSSOCN;Initial Catalog=ServerTestData;User ID=**********;Password=********;"

    I've tried using the ip address, error = that target machine actively refused connection. After posts gave database FULL user rights. changed ip to machine name, error = An attempt to login using sql authentication failed. Server is configured for windows authentication only.
    I then created a new user with mixed authentication and password, using connect string as above and same error occurs.
    The connection string used for windows authentication also gave error as above where I used Trusted_Connection=yes.

    All ports and exe's is allowed in firewall, ping works fine etc. As I said, I've read a lot of posts and done about everything that was suggested. I can however gain access to database from the server with a connection string with no password or user id, but using Trusted_Connection=yes.

    Any ideas from anyone?
    Monday, September 07, 2009 1:38 PM

Answers

  • I fail to see why you specify both instance name and port number. That is not how you should do it. Either you specify instance name or port number - specifying both is redundant and misleading. Anyhow, for your first two, SQL server will try to connect to your named instance. Most likely you didn't configure your SQL Server to allow non-trusted connections. You can do that in SSMS, right-click your instance and the security page. Requires a re-start of the instance.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, September 08, 2009 9:39 AM

All replies

  • Is there any chance that there are multiple instances of SQL Server on the host OLDPC? I'm thinking that there is a default instance running on Port 1433 which your application may be attempting to connect to.

    Just a thought...


    every day is a school day
    Monday, September 07, 2009 1:49 PM
    Moderator
  • I have the default instance and created another called ISRSDMS as in my connection string above. Do you think these are "colliding" on the same port 1433? Just to confirm, when i've installed server 2008 EE, I was asked for an instance name. The default was MSSQLSERVER, by which i've cleared the text box and named it ISRSDMS. Will this constitute another created instance?
    Monday, September 07, 2009 2:38 PM
  • They won't be coliding as they should both be listening on separate ports (unless you have configured them to use the same port in which case, i'd guess only one of them will start).

    It sounds like you have 2 instances on your machine and the default is listening on port 1433. Although you've indicated the instance name in your connection string, the fact that you have specified a port may overrule this and go to the default instance.

    I would check which ports your instances are listening on. Use the following link to help you do this:

    http://msdn.microsoft.com/en-us/library/ms177440.aspx


    every day is a school day
    Monday, September 07, 2009 2:43 PM
    Moderator
  • OK. I've followed your link, cleaned all previous port/firewall settings and started afresh. I've followed instructions on setup of sql AND firewall ports. I then tried to connect to the database as per link from sql2008 EE to the server. Login failed error given again. I've then tried to login from my app, still same error - login failed.
    I am now at my wits end. There is no problem with the connection I think, because if I try to connect from client pc using sql 2008 EE, it actually find the database in the server name as well as the server name etc. the problem definately lies in the firewall or sql settings, but where? As i've said, I went and did the exact same as the tutorial and login still failed. (this included giving my instance name a differant port to read from)

    Any more ideas? Thanks a million for help thus far.
    Monday, September 07, 2009 4:16 PM
  • Reading your connection string, you specify a port. This will override the instance name and try to connect to whatever instance listening on port 1433. You also don't specify a windows login (trusted connection), so if the instance listening on port 1433 don't accept SQL Server authentication, then the connection will fail. I would start by cleaning up the port thing (either specify only server name and port number - after verifying port no in SQL Server Configuration Manager, or specify server name and possibly instance name). As a troubleshooting measure, you can now configure your SQL Server to run in mixed mode and see whether the authentication is accepted. If this doesn't help, perhaps you can post the current (cleaned up) connection string and we can take it from there.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, September 07, 2009 7:07 PM
  • Thank you for all the help thus far from all. Below is the following connection strings. I have specified my port to listen on as 49172 for OLDPC/ISRSDMS to try and skip the default port of 1433 as per the help link above. Also please note that sql 2008 shows my default as OLDPC\ISRSDMS. I have assigned this name to the server when installation took place. I have also set my firewall settings to listen on 49172 as per help link above.

        MyConnObj.Open "Provider = SQLNCLI10;Data Source=OLDPC\ISRSDMS,49172;Network Library=DBMSSOCN;Initial Catalog=ServerTestData;User ID=AndreDataLogs;Password=********;"
    Error = "Login failed for user 'AndreDataLogs'." This is a user I have added with mixed authentication including a password and username.

        MyConnObj.Open "Provider = SQLNCLI10;Data Source=OLDPC\ISRSDMS,49172;Network Library=DBMSSOCN;Initial Catalog=ServerTestData;Trusted_Connection=yes;"
    Error = ""Login failed for user 'OLDPC\Guest'." This is with the trusted_connection=yes authentication.

        MyConnObj.Open "Provider = SQLNCLI10;Data Source=OLDPC\ISRSDMS,1433;Network Library=DBMSSOCN;Initial Catalog=ServerTestData;Trusted_Connection=yes;"
    

    Gives me a timeout error on port 1433 used for listening. When I remove the \ISRSDMS part on port 1433 same error occurs. When I remove \ISRSDMS and listen on port 49172 I get the login failed for user 'OLDPC\Guest' error.

    My errorlog file says that I'm trying to connect using sql authentication whilst the server is set for windows authentication. Is this not maybe where my problem lies?
    Tuesday, September 08, 2009 8:27 AM
  • I fail to see why you specify both instance name and port number. That is not how you should do it. Either you specify instance name or port number - specifying both is redundant and misleading. Anyhow, for your first two, SQL server will try to connect to your named instance. Most likely you didn't configure your SQL Server to allow non-trusted connections. You can do that in SSMS, right-click your instance and the security page. Requires a re-start of the instance.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, September 08, 2009 9:39 AM
  • Thank you Tibor. You were right, the non-trusted connection was not allowed. I am able to connect now.
    • Marked as answer by AndreRet Tuesday, September 08, 2009 9:53 AM
    • Unmarked as answer by richbrownesqModerator Tuesday, September 08, 2009 10:19 AM
    Tuesday, September 08, 2009 9:53 AM