locked
SQL Server on Win7, IIS on Server 2008 R2 - Credentials not auth RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have SQL server installed in a Windows 7 box. I have my website which accessed the SQL server installed in Windows Server 2008 R2. The problem is the WIndows & box wont accept the credentials from the NT Server...

    Windows 7 user with admin rights called  SQLWIN7/bob  

    Logged into Server 2008 as  SVR/admin

    I did an experiment and attempted to access SQLWIN7/c$  it asked for credentials so I entered  SQLWIN7/bob and pw - access denied

    I then attempted to access  SVR/C$  from SQLWIN7 as SVR/admin - success

    So it appears SQL server cannot access the Windows 7 Machine using a Windows 7 admin account!! This is why I can get SQL server authentication working

    Any suggestions for this?

    ASP.NET Impersonation disabled

    Server=192.168.1.6;Database=socks;User ID=SQLWIN7\bob;Password=●●●●

    Login failed for user 'SQLWIN7\bob'.

    Saturday, July 16, 2011 2:59 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Server=192.168.1.6;Database=socks;User ID=SQLWIN7\bob;Password=●●●●

     

    Login failed for user 'SQLWIN7\bob'.


    First, note that a Windows account cannot be specified in the connection string.  To connect using Windows authentication, omit the User ID and Password and instead specify "Integrated Security=SSPI":

    Server=192.168.1.6;Database=socks;Integrated Security=SSPI

    Since you are running your web code with impersonate=false, you must use SQL authentication.  To connect with SQL Authentication, the User ID an Password specifies a SQL login rather than a Windows account.  You need to create the account in SQL Server and grant the needed permissions before it can be used.  The SqlClient connection string will be like:

    Data Source=192.168.1.6;Initial Catalog=socks;User ID=bob;Password=●●●●

     

     

     

     

     

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Mr. Wharty Sunday, July 17, 2011 2:35 AM
    • Marked as answer by Stephanie Lv Tuesday, July 26, 2011 9:09 AM
    Saturday, July 16, 2011 4:26 PM