?
Solved

SQl connection string using AD user

Posted on 2012-09-12
16
Medium Priority
?
566 Views
Last Modified: 2012-09-18
Hello i have a .NET project and a AD user that i granted access to in the MSSQL server. in my .net code i have a connection string and it works fine on my local box but when i upload it to the server it try's to use the servers user to connect to the database
0
Comment
Question by:Chris Jones
  • 8
  • 4
  • 2
  • +1
16 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 38392054
Is this a web application?
If the user that runs the application is not the one you gave access to the server, you should use SQL Server Authentication on your connection string.

Create a SQL Login and grant him the necessary access, then use a connection string without trusted connection (windows authenticaction) and you should be able to log in without a problem.

For the connection string you can always go to: http://www.connectionstrings.com/
0
 
LVL 13

Expert Comment

by:Naman Goel
ID: 38392056
The only way to use is Impersonate user in app pool or IIS server web config and then use.
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 38392171
hello this is a web application and here is my connection st8ring - user info

    <add name="MAGT" connectionString="Data Source=HASQLSERVER;Initial Catalog=DATABASE;Persist Security Info=True;integrated security=SSPI;User ID=USER;Password=*********"
      providerName="System.Data.SqlClient" />

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 13

Assisted Solution

by:LIONKING
LIONKING earned 2000 total points
ID: 38392246
If you want AD security you need to remove the user/pass section of your connection string

    <add name="MAGT" connectionString="Data Source=HASQLSERVER;Initial Catalog=DATABASE;Persist Security Info=True;integrated security=SSPI;"
      providerName="System.Data.SqlClient" />

Open in new window


This will only use your specific user if you impersonate it, as said by naman_goel.
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 38392613
so how will teh code know  to use the AD account that i created for it.
0
 
LVL 13

Assisted Solution

by:LIONKING
LIONKING earned 2000 total points
ID: 38392638
Through Impersonation, like stated by naman_goel.

Maybe this will help you a bit

http://msdn.microsoft.com/en-us/library/xh507fc5(v=vs.100).aspx
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 38392714
oh ok i see that link did help so this would need to go in the IIS servers web config file so can i define multiple users in the web configl file.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38393005
Remove the
integrated security=SSPI

from connection string.
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 38394899
hmm that worked but its telling me that the usre cant login and its my cradentals so i know its correct.
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 38395028
i get this error but it did not show what user it tried.


Login failed for user ''.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38395176
That will not work for an AD account... See this

Link
0
 
LVL 1

Accepted Solution

by:
Chris Jones earned 0 total points
ID: 38395188
oh i fixed it

here is my connection string

    <add name="MAGT" connectionString="Data Source=HASQLSERVER;Initial Catalog=MagnusHealthT;integrated security=SSPI;Persist Security Info=false;Trusted_Connection=Yes;"
    providerName="System.Data.SqlClient" />

then i had to go to the server and give that user access to the .Net folder on the system
then i set this

    <authentication mode="Windows" />
    <identity impersonate="true"
    userName="ctis\*****"
    password="*********" />
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38395192
That AD user has been added to SQL Server security right?
0
 
LVL 13

Expert Comment

by:Naman Goel
ID: 38395198
superb
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 38396948
yes the user is in MSSQL
0
 
LVL 1

Author Closing Comment

by:Chris Jones
ID: 38408795
thanks everyone
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question