Link to home
Start Free TrialLog in
Avatar of RTSol
RTSol

asked on

SQL server authentication

Hi,

I have a scenario where I have two servers, one running IIS and one running SQL server 2014. There are two types if clients, one running a web browser against a web application on the server running IIS and one running a local WPF application against a local database which should be merge replicated to the database in the server running SQL server 2014 - see the attached picture. None of the machines belong to any domain. the SQL databases are all setup with FILESSTREAM enabled since they are handling files. This means the authentication against the SQL server has to be Windows authentication.

Right now I have no idea how to configure this. Kerberos?

Please help!

best regards
RTSOL
System-setup.jpg
Avatar of CodeJunky
CodeJunky
Flag of United States of America image

Is there any reason that you don't have them on a domain?
you could use Kerberos; but also you could use SQL Authentication as well.
Avatar of RTSol
RTSol

ASKER

Hi,

The servers could possibly be in a domain but the clients could be anyehere in the world in a local work group.

Ok - i am a novice in this field so correct me if I am wrong.

SQL authentication doesn't work since the SQL servers have FILESTREAM enabled.

-RTSOL
Yes that is kind of correct.  FileStream does require windows authentication; but from the SQL Server service.  So it is possible to have clients access SQL Server via SQL Authentication and the SQL Server access the FileStream via Windows Authentication.
One more comment about FileStream.  While it may be possible to do this I think I would prove cumbersome to build out.  Are the clients accessing the data via IIS?  If so you could put IIS on the domain with a domain account, along with your SQL Server.
Avatar of RTSol

ASKER

Sounds interesting - how is that done? The connection string at the client might look something like this:

Data Source=server IP;Initial Catalog=dbDatabase;Persist Security Info=True;User ID=user;Password=password;Connect Timeout=120

I am already using Filesstream but in a much simpler setup. IIS and SQL on the same server and no access from local WPF applications. It works fine.

My issue is connecting from the client to the server with Windows authenitcation. The two servers could possibly be put in a domain if nessesary.

-RTSOL
If you want the clients to connect to SQL using windows authentication then you will need to have them on the domain.  another option may be to have the clients log into a client that is on the domain; but that would require the clients to either share a domain account or add each one to the domain.
Avatar of RTSol

ASKER

What about Kerberos?
That may work also; but I have not worked with that before.  Sorry.
Avatar of Vitor Montalvão
RTSol, do you still need help with this question?
Avatar of RTSol

ASKER

Hi and thanks for the attention,

Yes - I still need some help. I have done some progress but I am lost in the security djungle.

In my server I have attached the client database via SQL authentication. I have made a snapshot in the SQL 2014 and it is residing on a share on the server. Now when I open the Replication Monitor I can see errors in the publication - I assume that has to to with security. I am not sure how to proceed - van you help?

Best regards
RTSOL
Which errors? Can you post them?
Avatar of RTSol

ASKER

Hi,

Error messages:
The process could not connect to Subscriber 'RTSOLLAP\SQLEXPRESS'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084)
Get help: http://help/MSSQL_REPL20084
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Source: MSSQLServer, Error number: 18452)

This client is connected via SQL login which obviously is the problem. Not the client nor the server belogns to any domain. What shall I do?

Best regards
RTSOL
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RTSol

ASKER

Hi,

Sorry for the delay but I had to make a small operation - which went well.

I read the blog post you mentioned and I also found this: http://www.replicationanswers.com/internetarticle.asp

I have to dig into this - thanks!

Best regards
RTSOL