September 16, 2015 at 1:43 pm
I have SQL Server 2012 Standard installed on Windows Server 2008 R2 Datacenter. SQL is set to allow mix mode sql/windows authentication.
I have a requirement to use Windows Authentication and wanted to know how I would go about to do that from another Windows Server 2008 R2 Datacenter server that is hosting a Web Application.
I am not using Active Directory or Domains, these are separate servers being hosted in Amazon Aws.
My connectionstring looks something like this, I want to know how to use Windows Authentication to another server.
<connectionStrings>
<add name="myConnect" connectionString="Data Source=192.00.0.3,1433;Initial Catalog=MyDatabase;Integrated Security=False;;MultipleActiveResultSets=True;User ID=user;Password=password" providerName="System.Data.SqlClient" />
</connectionStrings>
September 16, 2015 at 2:44 pm
djacobos (9/16/2015)
I have SQL Server 2012 Standard installed on Windows Server 2008 R2 Datacenter. SQL is set to allow mix mode sql/windows authentication.I have a requirement to use Windows Authentication and wanted to know how I would go about to do that from another Windows Server 2008 R2 Datacenter server that is hosting a Web Application.
I am not using Active Directory or Domains, these are separate servers being hosted in Amazon Aws.
My connectionstring looks something like this, I want to know how to use Windows Authentication to another server.
<connectionStrings>
<add name="myConnect" connectionString="Data Source=192.00.0.3,1433;Initial Catalog=MyDatabase;Integrated Security=False;;MultipleActiveResultSets=True;User ID=user;Password=password" providerName="System.Data.SqlClient" />
</connectionStrings>
If you're not using a domain, then I can only think of one way.
First, you have to create a Windows Local User on the SQL Server box.
Second, grant that Windows user access to the SQL Server.
Third, in your connection string, you're going to have to specify the computer name as part of the username. So if your SQL Server is named SQLBox and your user is named AppUser, then in your connection string it would be SQLBox\AppUser. Make sure you separate the computer name and the user name with the backslash.
This lets the SQL Server know where it can look for authentication for the user.
September 16, 2015 at 3:53 pm
To clarify things in case I did not. The SQL Server is on a separate Win 2008 R2 than the Web Server (WIN 2008 R2).
The connectionstring for Windows Authentication does not allow for user/pass, it looks like this:
<connectionStrings>
<add name="myConnectionString" connectionString="Data Source=serverAddress;Initial Catalog=myDatabase;Integrated Security=SSPI"/>
</connectionStrings>
September 17, 2015 at 6:46 am
I'm not really sure this is possible. The issue is that in a domain with active directory, you have a third server (the domain controller) that validates the user to all the other machines. Without a domain, each server is on its own and they each have their own user list.
I've been reading up on IIS impersonation, but I think the situation is designed so that the same credentials are used for the IIS authentication and the SQL Server authentication, which isn't going to work because you're trying to use 2 separate user accounts.
Unfortunately, this is a very specific scenario that most people aren't going to run in to, so trying to find the magic work around through google isn't going to be easy.
I'm not going to claim that this scenario can't be done, because I don't know that you can't, but there probably won't be very many people out there that have attempted this. I will say that if this is really something that's important to do, you're probably going to be ahead of the game if you just call Microsoft tech support and get the official answer rather than spend an unknown amount of time trying to figure it out.
September 17, 2015 at 7:16 am
Hi
Let's clarify something. Do you need Windows authentication to authenticate users in a Web application or/and on SQL server?
Best Regards
Mike
September 17, 2015 at 8:16 am
I am getting this requirement from DoD DISA STIG for SQL Server 2012
SQL Server must be configured to use Windows Integrated Security. SQL Server Authentication does not provide for many of the authentication requirements of the DoD. In some cases workarounds are present, but the authentication is not as robust and does not provide needed functionality. Without that functionality, SQL Server is vulnerable to authentication attacks. Consideration must be given to the placement of SQL server inside a forest to ensure evaluation of risk within the environment is considered. Risk includes introduction of risk to SQL Server from other applications or workstations as well as risk from introduction of SQL server itself into an established environment.
I am using a Web Application that makes a connection to SQL and performs read/add/update/delete transactions on the data. I am also logging on remotely to Windows Server 2008 that host SQL Server 2012..I would assume both from Web and SQL Server.
September 18, 2015 at 5:08 am
Hi
Imho join both servers to the domain is the best solution in this case.
Of course you can leave it as is, but it requires significant administrative effort involved in keeping up to date local SAM on both servers.
Br.
Mike
September 18, 2015 at 6:45 am
You're also going to have to check the policy on password changes. If the password, for example, gets changed every 90 days, the application breaks because it can't connect. You're going to have to go into your web.config and change the password to the new one.
You can use a Windows account local to the SQL Server, but I have to agree that the best way is to join a domain. If your SQL login ever has to do anything outside of the SQL Server box itself, it's going to need a domain account and permissions to whatever network resources it needs to access. This isn't going to happen if you're using a local account.
Now, I don't have any idea what your application is or what your requirements are. You might not need any network permissions at all today, but you might need them tomorrow. Applications have a way of expanding in scope and functionality.
July 25, 2016 at 12:23 am
Please help, how windows authentication to connect the web application?
In my case, there are two different server and both the servers are member of same domain group.
SQL 2012 DB server - windows authentication and I have separate domain id(CNT\jmsapps.admin) also added to SQL instance side for login the OLTP database. this domain id have minimum rights for Insert, update, execute and connects.
step 1: I logged DB server with domain id (CNT\jmsapps.admin) through RDP and automatically connect SQL instance by using windows authentication also can able operate DML action in object level in particular database.
Step 2: how can we achieve in application server by using the following connection string method and hosting web application access to the end users client side?
a. In Application server, Does is it require add that domain id administrator group and login the server with same domain id?
b. Total 100 domain users are access web application, Does is it require to add all the domain id in SQL instance login for access web application? or adding one single domain group id at SQL login and mapping database assign minimum rights?
c. there are some interface configured this database and connected to other client, do we add domain id in SQL login who are accessing client?
Pl. advise and suggest.
<connectionStrings>
<add name="myConnectionString" connectionString="Data Source=servername;Initial Catalog=DatabaseName;Integrated Security=SSPI"/>
</connectionStrings>
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply