how to connect a remote SQLserver using widows authentication from a .net application, if possible

Question

currently I am using this connection string inside the app.config file of the application

add name="LightSailEntities" connectionString="metadata=res://*/LightSailEntities.csdl|res://*/LightSailEntities.ssdl|res://*/LightSailEntities.msl;provider=System.Data.SqlClient;provider connection string='data source=abc.xyz.com;initial catalog=LightSail;user id=LightSail; password=yourpasswordhere;MultipleActiveResultSets=True;App=EntityFramework'" providerName="System.Data.EntityClient"

The domain of .Net application and the domain of client, using .Net application, is different from domain of SQL server. I mentioned "using widows authentication" only because of, I have the access of the server machine(means I can use Remote Desktop Connection) on which the SQL server is installed.

What have you tried? You shouldn't face any problem if the firewalls are set right — nunespascal, Jan 03 '13 at 09:52
is your windows account added to the security table inside sqlserver? — CuccoChaser, Jan 03 '13 at 09:55
WHy you give a password? Integrated security is no password and username, process must run in a AD integrated account. Not doing that, it is not possible. — TomTom, Jan 03 '13 at 10:09

score 2 · Answer 1 · answered Jan 03 '13 at 10:07

For Windows Auth you don't need to set the User Id and Password but you do need to include 'Integrated Security=SSPI;'

Try:

add name="LightSailEntities" connectionString="metadata=res://*/LightSailEntities.csdl|res://*/LightSailEntities.ssdl|res://*/LightSailEntities.msl;provider=System.Data.SqlClient;provider connection string='data source=dev.shopcube.com;initial catalog=LightSail;Integrated Security=SSPI;MultipleActiveResultSets=True;App=EntityFramework'" providerName="System.Data.EntityClient"

There's a bit more info here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.71).aspx

For anyone who's interested, there is a difference between 'Integrated Security = True' (as mentioned by @Marcin) and 'Integrated Security = SSPI'. The details are covered here: http://stackoverflow.com/questions/1229691/difference-between-integrated-security-true-and-integrated-security-sspi — Vaze, Jan 03 '13 at 11:21

score 1 · Answer 2 · answered Jan 03 '13 at 11:44

You have to change ConnectionString to use Integrated Security=SSPI insetad of user and password

add name="LightSailEntities" 
connectionString="metadata=res://*/LightSailEntities.csdl|res://*/LightSailEntities.ssdl|res://*/LightSailEntities.msl;
provider=System.Data.SqlClient;
provider connection string='data source=dev.shopcube.com;initial catalog=LightSail;Integrated Security=SSPI;MultipleActiveResultSets=True;App=EntityFramework'" 
providerName="System.Data.EntityClient"

After that, look at the Identity set for the Application Pool of you application.
That user must be authorized to access your DB using Security\Logins inside Object Explorer pan of Management Studio.

score 0 · Answer 3 · edited Jan 03 '13 at 10:55

Youo can use following code:

SqlConnection conn = new SqlConnection(Configuration.DBConn);

or if you use Linq2SQL:

DBContext ctx = new DBContext(Configuration.DBConn);

where in Configuration class DBConn string contains connection string to sql ie:

Data Source=XYZ\\DEV;Initial Catalog=YOURDB;Integrated Security=True;Connect Timeout=600;connection lifetime=600

Integrated Security=True tells that you want to use windows auth.

how to connect a remote SQLserver using widows authentication from a .net application, if possible

3 Answers3