Another question about Login failed, but with Windows NT Integrated security for SQL 2005

Another question about Login failed, but with Windows NT Integrated security for SQL 2005

• kg81

   

  Hallelujah!

  After struggling for another day, I have finally found a way to get around the defect:< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

   

  1. Install a default instance (NOT a named instance that I prefer and had been using).
  2. Add a new login using Windows Authentication by using search-advanced-find now to find the Guest on the local machine folder.
  3. I am so frustrated that I am giving every permission that I can find to that login (I just want to get things going).

   

  Now I can connect to the default instance of SQL 2005 from another machine without any problem.  

   

  I do not know what kind of more issues lying ahead.  At this point of time, I have to admit that migration from SQL 2000 to SQL2005 is a mistake (I also have had other connection issues within Visual Studio for data mining projects.  I also noticed that many posted messages regarding connection problems at various places have never been answered).  For those watching, you may want to hold the migration to SQL 2005 at least till the first SP comes out to fix the problems of the most basic functions such as connection.

   

  Thanks again for all your attention.

   

  H

  
  
  

• Christophe Bauwens

  well i haven't solved it yet but i did find a  setting in GPEDIT.msc that was "local users authenticate as guest" and i changed it to local users authenticate as themselves. That was under security settings... still looking for answer on this.

  --must be getting somewhere, when i try to map a drive it doesn't automatically pop up as guest now. But it won't accept the passwords i've set.

   

  On my router, i have netbios-ssn and microsoft-ds open... anything else i'd need for a password auth

  *it just keeps prompting for a password, i tried resetting my password and no go... tried making a new user account that was limited... no go.

  
  

• paul Dierick

  How are the machines set up Also, have you created already a Windows login

  Looks like the first machine is not known to the second, so you end up connecting as Guest to it. You should ensure that the account from the other machine is known on the SQL Server machine and also that it is granted SQL Server access.

  Thanks
  Laurentiu

  
  
  

• solmyr72

  Why doesn't named instance work, but default instance works.  Is this also related to the network setup

  While I could use a .udl file to connect to a named instance by user name and password,  I could not use the Management Studio to connect to the server in the same way as I mentioned in one of my previous messages; I also had funny connection problems in BI Development Studio at that time.  From my limted experience of programming a bunch of socket based applications, the user name and password based connection between a client and server has little dependence on the computer configuration as long as the ports are open and the required protocol (e.g. TCP/IP) is supported. If one client (e.g. .udl) can connect, I cannot imagine how a network setup prevents other clients from connection using exactly the same method.  For this reason,  I cannot help making the conclusion that SQL MD and BIDD have bugs in their database connection part.

  Well I have certainly spent enough time addressing the connection issue in the past week.  I apologize if I have wasted a lot of time of other people by drawing their attention but not benefiting them in anyway.

  I want to emphasize that I DO appreciate the prompt adivce that received from this forum.  My sincere gratitude!

  So long!

  H

  
  
  

• ecoloney

  I forgot to mention that now I cannot use the Management Studio to connect to the SQL 2005 on PC A from PC B using SQL Server Authentication even though I can do it in my .Net based program or a .udl file.

  The error message I get from the management studio is as following:

  Login failed for user 'test'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

  For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476

  ------------------------------
  BUTTONS:

  OK
  ------------------------------
  

  
  
  

• Martin Hueser

  Are the two machines in the same domain, or workgroup

   

  Thanks,

  George.

  
  
  

• thedba

  Try setting the same account with the same password on both machines. For example, have user Alice on both machines with the same password. The you should be able to connect as Alice from one machine to the other.

  Thanks
  Laurentiu

  
  
  

• Philip Galea

  For your first question, could you include the connection string that you were using

  For the error message that you mentioned in your previous post, when attempting to use SQL Authentication, it indicates that SQL Auth is not enabled and that the server is configured to use Windows Auth only.

  You have mentioned three different issues:

  1) Windows Auth did not succeed from one machine to another; connection was made as Guest instead. This could be because the account from machine A is not present on machine B or has a different password.

  2) Connection to a named instance failed but works for the default instance. This could be related to the connection string. What error do you receive, if any Also check the server errorlog for additional information.

  3) UDL connection worked but Management Studio failed to connect saying mixed mode is not enabled. Check that mixed mode is indeed enabled and check the UDL file to confirm it is using SQL authentication.

  Thanks
  Laurentiu

  
  
  

• Carl-Otto

  The fact that you ended up connecting as Guest on the machine is not SQL Server related, it can only depend on the way you've set up the connection between the two machines. SQL Server cannot change the identity with which you attempt to log on.

  Thanks
  Laurentiu

  
  
  

• Herru Perdana

  That is exactly what I have been doing.  It used to work flawlessly when I was running SQL 2000 on W2K box.  According to the error message "... Loging failed for user 'PCB\Guest'" received when using Windows NT Integrated security, it seems the guest user ID is used, not the user ID I use to log on the computer (e.g. Alice) is used to log in.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

   

  I really wish there was an option to strip off all the security protection of SQL2005 because I do not need any security at all for my local operation within the workgroup.

   

  Another note: when I was trying to create a data source in Visual Studio 2005 with Connection manage, I found neither ."SqlClient Data Provider" nor SQL Native Client works.  However, Microsoft OLE DB Provider for SQL works wll.

   

  H

   

  
  
  

• BrettM

  They are in the same workgroup called "Home".  I do not have a domain.  Every other aspect of the workgroup works fine.

  H

   

  
  
  

• fleacricket

  I'm having the same problem basically... i think. When i use 127.0.0.1 (loopback) i can log in fine, but if i use my external wan IP, i cannot connect, i get sql exception... "Login failed for user ''. The user is not associated with a trusted SQL Server connection."

  here's the connection string that works:

  const string STR_CONNECTION_STRING =

  "Data Source=127.0.0.1\\SQLEXPRESS;" +

  "Initial Catalog=northwind;" +

  "Integrated Security=True";

  const string STR_CONNECTION_STRING =

  "Data Source=64.126.81.34\\SQLEXPRESS;" +

  "Initial Catalog=northwind;" +

  "Integrated Security=True";

  Interestingly, when i try to MAP a shared drive remotely on my machine, no matter what i do it always uses the guest account, not my administrator account that i'm logged in as... so that could be part of the issue.

  
  

• must_be_a_unique_name

  I spent a lot of time today exploring this issue and am ready to give up because at least I can use SQL server authentication to get things going.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

   

  Here is the very simple set up:

   

  PC A: Windows XP Pro with SP2, SQL 2005 Standard Edition.

  PC B: Windows XP Pro with SP2

   

  These two PC’s are connected via a home network, which I believe is the culprit of everything.

   

  I spent a lot of time reading about XP’s security setting, which is so convoluted that one can write a thick book to just explain it.  Here are a few things among many that I have found:

   

  1. Home network requires Guest account enabled to share files (please don’t ask me why because I do not understand it).
  2. When I disable the guest account via a convoluted way and try to make a connection to SQL 2005, I get an error message regarding login of user ‘’ (I assume the user ID is blank in this case). I gave up immediately because I need the guest account to share files.
  3. After I restored the guest account, I created an account named “Guest” on the SQL server and gave it every privilege that I could find.  I still could not make a connection with Windows authentication.
  4. I tried to create an account named “B\Guest” on PC A, but XP thinks the name is illegal.

   

  If there is a way to use the Windows authentication, it would be great. Otherwise, as I said, I can live with what I have now.  Thank you very much anyway!

   

  Best,

   

  h
  
  
• Another question about Login failed, but with Windows NT Integrated security for SQL 2005