|
|
You're in the
sql server connect
group:Use Windows Integratd security
sql server connect:
second domain DOM2. The sql server runs under DOM1 domain admin acount. I have a DOM2 domain logon which I use to log in my XP, Using sql authentification works fine, but very soon I need to use Windows integrated security I tried to connect to sql by doing the following : I have an admin account on the domain DOM1 which I use to do "Net use ....IPC$" on the sql server from XP as I said earlier, The sql server runs under DOM1 domain admin acount My DOM1 admin account is in the local admins group of the sql server Of course the "Connect to server" dialog box displays server info and in the user name text box, I find DM2\myXPlogin click OK, I get the following error : The user is not associated with trusted sql server connection. Switching to log view in sql, I find : *************************************** SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.40.0.25] *************************************** How can I make this go through?
Hello,
I understand that you are not able to log into SQL 2005 from a machine in different domain. You encountered error regarding integrated security. If I'm off-base, please let me know. To understand the issue better, I'd like to confirm if DOM1 and DOM2 are trusted. I know you have used "net use ...IPC$" to connect from remote xp client to SQL Server by using domain admin account fo DOm1 and this seems to indicate there is no trust relationship between 2 domains. In order to isolate the issue, please let me know the following information: 1. Did you enable Named pipes on both client/server to test? 2. Are the 2 domain in the same LAN without firewall etc on the edge? 3. If you add a local user with the same dom2 user and password on SQL 2005 and add it to local admins group, can you access the server from client by using Windows authentication? If you have any update, please let's know. We look forward to your reply. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Community Support ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at <http://msdn.microsoft.com/subscriptions/support/default.aspx>. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Correct, there is no trust relationship between the 2 domains, that is why I
use "net use......", regarding your questions/instructions, here we go 1- Yes, Named pipes are enabled on both sides 2 - No, the 2 damains are not on the same LAN 3- I followed the instructions in step 3, I managed to go through. So what would be the solution or the fix fopr that in order not to be obliged to add each user as in step 3. Thanks for your help [quoted text, click to view] "Peter Yang [MSFT]" wrote:
> Hello, > > I understand that you are not able to log into SQL 2005 from a machine in > different domain. You encountered error regarding integrated security. If > I'm off-base, please let me know. > > To understand the issue better, I'd like to confirm if DOM1 and DOM2 are > trusted. I know you have used "net use ...IPC$" to connect from remote xp > client to SQL Server by using domain admin account fo DOm1 and this seems > to indicate there is no trust relationship between 2 domains. > > In order to isolate the issue, please let me know the following information: > > 1. Did you enable Named pipes on both client/server to test? > > 2. Are the 2 domain in the same LAN without firewall etc on the edge? > > 3. If you add a local user with the same dom2 user and password on SQL 2005 > and add it to local admins group, can you access the server from client by > using Windows authentication? > > If you have any update, please let's know. We look forward to your reply. > > Best Regards, > > Peter Yang > MCSE2000/2003, MCSA, MCDBA > Microsoft Online Community Support > ================================================== > Get notification to my posts through email? Please refer to > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif > ications > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>. > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues > where an initial response from the community or a Microsoft Support > Engineer within 1 business day is acceptable. Please note that each follow > up response may take approximately 2 business days as the support > professional working with you may need further investigation to reach the > most efficient resolution. The offering is not appropriate for situations > that require urgent, real-time or phone-based interactions or complex > project analysis and dump analysis issues. Issues of this nature are best > handled working with a dedicated Microsoft Support Engineer by contacting > Microsoft Customer Support Services (CSS) at > <http://msdn.microsoft.com/subscriptions/support/default.aspx>. > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Hello,
Thank you for your update. After consulting internal resource, I have got the following feedback: ========================= Unfortunately you can not successfully connect to remote SQL Server using Y\domain user as z doesn't trust Y. If you are planning to use Windows Authentication, then the only alternative is to create the same local user account (with the same password) on both client and SQL server machines and use this user account to register SQL Server on the client machine. It doesn't help if you already have a connection to \\SQLServer using net use as its valid only for browsing purpose. When you try to register SQL Server in Enterprise Manager, Enterprise Manager impersonates the logged on user (in your case, it is z\someuser) and fails as the remote SQL Server doesn't trust this domain user. ========================= I will setup a test environment to test the issue on my side to see if I'm able to reproduce the problem. If it is reproduced, I will send a product feedback to proper channel so that they may consider a change. Thank you for your patience. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Hello,
I performed a test on my side and I was able to reproduce the issue. I logged on as a local user of client machine and tried to connect to SQL Server 2005 in a test domain. 1. Logged on as local user, and connectted to SQL 2005 server via a SQL login, it worked fine 2. Created the domain user with same user name/password and I can connect to SQL Server properly via Windows authentication. 3. Logged on as different local user, and add the ipc$ share "net use \\<server>\ipc$ /user:test\testusr1 password", and it succeeded. 4. Tried to connect to SQL 2005 via Windows authentication, I receveied "The user is not associated with trusted sql server connection". In event log I found the exact error message "SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security" Based on my experience, this seems to work in SQL 7. I think this design change here is for security purpose since using existing security channel here may cause some security risks. I agree that it will be overhead to add each user to the remote domain, and please rest assured that I have forwarded your feedback to the product team. Also, I encourage you submit via the link below and our product team would like to hear your voice: http://lab.msdn.microsoft.com/productfeedback/default.aspx Another option is to use "Run as" feature in Windows, you may right click the Management studio->Run as, and try to run the application as remote domain to see if it works for you. If you have further questions or concerns, please feel free to let's know. Thank you. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Hello,
This is YanHong from Microsoft MSDN Support Group and I am the manager of Newsgroup Support Team. Peter Yang escalated the issue to me. We discussed the issue together. Peter has set up an environment on our side and reproduced the exact problem before. So we contacted product team on it. The development team replied the reason of this behavior and provided a workaround. Peter posted that in the newsgroup then. I quite understand your concern here that adding all username and password is not convenient. We have forward the scenario to our product group. As Peter mentioned, you may also directly submit feedback at our connect web site: http://connect.microsoft.com/site/sitehome.aspx?SiteID=68 Another possible way is to write some code to read username/password and then add them as users to SQL server in a batch. Since we can¡¯t read username/password from Windows directly, it also needs some manual work to store username/password somewhere first. If you need any help on writing such code, please feel free to post in our newsgroup and we will follow up. Last but not least, if this problem is critical for you, you may contact our support service with the business impact. Our support engineer will work with product group to see whether we can help you further. Our support service can be reached at: http://msdn.microsoft.com/subscriptions/support/default.aspx. Thanks very much for your understanding. Sincerely, Yanhong Huang Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Hello,
[quoted text, click to view] You wrote: "The development team replied the reason of this behavior and provided a workaround. Peter posted that in the newsgroup then." Do you have a link to that posting? Thanks Danny
Hello Danny,
I have included the content from the original post also in this thread: ========================= Unfortunately you can not successfully connect to remote SQL Server using Y\domain user as z doesn't trust Y. If you are planning to use Windows Authentication, then the only alternative is to create the same local user account (with the same password) on both client and SQL server machines and use this user account to register SQL Server on the client machine. It doesn't help if you already have a connection to \\SQLServer using net use as its valid only for browsing purpose. When you try to register SQL Server in Enterprise Manager, Enterprise Manager impersonates the logged on user (in your case, it is z\someuser) and fails as the remote SQL Server doesn't trust this domain user. ========================= Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |