0

We're planning to move a large portion of our database to our web host's SQL server. The SQL server is only accessible through SQL logins. Is there any way to set something up locally that would recognize users' windows credentials and automatically provide them access to the remote database?

This would make it easier on the users since they wouldn't have to come up with yet another set of login credentials, and it would be easier on me since I wouldn't have to maintain them separately.

The simplest solution I imagined would be to store the login credentials in some shared location only accessible by people who should be allowed to access the database. However, this strikes me as an extremely naive solution, and possibly full of security holes.

Another option that crossed my mind would be to use a web interface hosted on our main server. Then only that site would need the login credentials. However, this wouldn't really work as well if we wanted to do something more advanced (say a windows forms app or Access-based reports).

Ideally I'd like some sort of SQL proxy, but google isn't being much help in that regard.

Any thoughts?

Cogwheel
  • 135
  • 4

2 Answers2

2

If you still have a SQL Server on your domain, you might be able to use a linked server as a proxy of sorts. You can define remote login mappings within the linked server definition, so you could map the local Windows logins to their remote SQL-authenticated counterparts.

Ed Leighton-Dick
  • 1,094
  • 1
  • 7
  • 12
1

Well, I dont know why you dont enable mixed-mode authentication. That would solve your issue, IMHO , without a domain requirement.

Once mixed mode is enabled then you disable the "sa" account, enable the tcp/ip port 1433 listener, then add the NT user accounts as users in SQL, and then edit the "public" server role and then add the NT users to the "public" role on the sql server with permission of "connect" (just like the "sa" user has). Finally, make sure the admin user, which owns the SQL server, is locked down so nobody can login except for the admin.

djangofan
  • 4,172
  • 10
  • 45
  • 59
  • Except that I'm not the admin of the server they'd be connecting to. We're only allowed to have three logins to the web-based sql server, and I don't have very fine-grained control over the security. – Cogwheel Oct 28 '09 at 23:10