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
RTSolAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Thunder724Commented:
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.
0
RTSolAuthor Commented:
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
0
Thunder724Commented:
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.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Thunder724Commented:
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.
0
RTSolAuthor Commented:
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
0
Thunder724Commented:
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.
0
RTSolAuthor Commented:
What about Kerberos?
0
Thunder724Commented:
That may work also; but I have not worked with that before.  Sorry.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
RTSol, do you still need help with this question?
0
RTSolAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Which errors? Can you post them?
0
RTSolAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I never worked with Replication between servers with no domain but went to see Microsoft's article about it and here what it says:
To use SQL Server Authentication:

◦Add a SQL Server account for each agent at the appropriate nodes (use the same account name and password at each node). For example, the Distribution Agent for a push subscription runs at the Distributor and makes connections to the Distributor and Subscriber. The SQL Server account for the Distribution Agent should be added to the Distributor and Subscriber.


◦Ensure that a given agent (for example the Distribution Agent for a subscription) makes connections under the same account at each computer.


◦In situations that require SQL Server Authentication, access to UNC snapshot shares is often not available (for example access might be blocked by a firewall). In this case, you can transfer the snapshot to Subscribers through file transfer protocol (FTP). For more information, see Transfer Snapshots Through FTP.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RTSolAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.