Windows Authentication with hosted SQL Server?

I have a client that is a private school that can't afford their own SQL Server. They can, however, afford to pay monthly for a SQL Server that is hosted on the internet (e.g. www.alentus.com)

Can I develop and deploy a WinForms application (foobar.exe) on the intranet that uses Windows Authentication with Active Directory to gain access to a SQL Server that is hosted on the internet (e.g. www.alentus.com)

Here's what I assume:
1) The WinForms application (foobar.exe) uses Windows Authentication with Active Directory to authenticate and authorized the user.
2) Foobar.exe connects to the SQL Server (using the SQLConnection object).
3) Foobar.exe sends requests for data to the hosted SQL Server by passing the username to the SQL Stored Procedure.
4) SQL Server assumes that the user is authenticated and authorized.
5) SQL Server receives the username as a parameter and returns the appropriate data. (The data returned is based on the username where username is the primary key).

Is this possible

Michael

Answer this question

Windows Authentication with hosted SQL Server?

sambath ramachandran
yes, that's possible if the place where the sql server is hosted has the appropriate ports open to communicate with sql server. probably is though that after the slammer virus went around, most places close up those ports.

Assuming they never need more than 2GB of data per database, they would be able to use MSDE on their server, which is the free version of SQL Server 2000 (with size and connection limitations, that usually aren't a big deal)

oops, didn't see Jacob's post :|
-JW

How many client installs do you expect them to have

>> 100 teachers and staff.

Could they use MSDE You could also use an open source DB for a (possibly) cheaper alternative. Something like MySQL or PostgreSQL. I believe that .NET providers have been written for both of these.

>> Yes, I've thought about mySQL but how well does it work with Visual Studio .NET I've seen a couple of .NET providers for MySQL.

>> If I had to choose, I would select MSDE over non-Microsoft open source databases.

Yes, that's possible if the place where the sql server is hosted has the appropriate ports open to communicate with sql server. probably is though that after the slammer virus went around, most places close up those ports.

>> I was only planning to use the usual port 80 but I should ask the SQL Server hosted provider to be sure.

Assuming they never need more than 2GB of data per database, they would be able to use MSDE on their server, which is the free version of SQL Server 2000 (with size and connection limitations, that usually aren't a big deal)

>> 2GB data limit is not a problem.

>> Yes, I've thought about MSDE. To get around the 5 connection limit, I could use web services through IIS, right

>> Curious, does MSDE enable/allow Master/Slave replication relationships

>> So, Windows Authentication with Active Directory on the client side will work regardless of whether SQL Server is hosted on the internet or MSDE is hosted on the intranet
WetRivrRat
How many client installs do you expect them to have Could they use MSDE You could also use an open source DB for a (possibly) cheaper alternative. Something like MySQL or PostgreSQL. I believe that .NET providers have been written for both of these.
netsql

This morning I started working on a prototype to demonstrate the feasibility of a Windows application that teachers can use for grading/attendance while using a SQL Server database on a hosted server.

First, I needed to get the user account name from Windows Authentication.

string myName = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();

So then, myName = MYDOMAIN\myuseraccount

I strip out 'MYDOMAIN\' to get the user account name and query the database to get information.

Works very nice.

MGB
Seth Wenzel
So close... It's 1434...
Alan Smith MVP
i thought there were a few different ports that sql server used for different services like 1430 through 1434 or something like...now that i think about it though, you're right, that's the primary port...so close is right :P
ChasP
Yes, it should work over the internet, no matter what, but again, Sql Server doesn't use port 80, so you'll have to check with the provider to make sure they have the Sql Server port open (i think it's port 1433)

As for MSDE, I think it would be your best bet, but you'd probably have to set up a little app to stress test it first to make sure you don't go over the max amount of connections. Connection pooling in .NET helps a bit with that too though.

And as for replication in MSDE, MSDE supports only being a replication subscriber, it does not support being a replication publisher.
KillerBoy

Yes, it should work over the internet, no matter what, but again, Sql Server doesn't use port 80, so you'll have to check with the provider to make sure they have the Sql Server port open (i think it's port 1433)

>> Ok, thanks for the clarification.

As for MSDE, I think it would be your best bet, but you'd probably have to set up a little app to stress test it first to make sure you don't go over the max amount of connections. Connection pooling in .NET helps a bit with that too though.

>> Ah yes, .NET connection pooling.  Good idea.  Just use the connection for the shortest time possible every time.

And as for replication in MSDE, MSDE supports only being a replication subscriber, it does not support being a replication publisher.

>> This private school has two separate physical campuses (elementary and secondary).  Each campus is connected by a WAN.  For some reason, connecting to a server from one side of the WAN to the other side of a WAN takes longer than connecting to the internet.  So, this brings up an idea (thus, the reason I asked about replication).

>> Visual Studio .NET comes with SQL Server 2000 (with a limit of 5 concurrent connections).  So then, install SQL Server 2000 on the left side of the WAN as a replication publisher.  Install MSDE on the right side of the WAN as an replication subscriber to keep the data in sync'd. (The school would have to purchase Visual Studio .NET anyway).  With .NET connection pooling, this system architecture should provide good performance.  If not, I can always revert to using a hosted SQL Server solution.

Michael
Windows Authentication with hosted SQL Server?

Answer this question