March 11, 2005 at 11:37 am
Through attrition and changing job responsibilities, I've been assigned as the "owner" of a production server running SqlServer. A software vender installed their application on this server and setup a single userid/password for that application (an Access application linked via ODBA to production SqlServer tables).
I want to change that so the users are validated using their existing Windows authentication instead of "sharing" this userid/password.
I've looked at the SqlServer Enterprise Manager & drilled down to Security - SQL Server Login Properties - New Login.
Where can I learn about the abilities and settings in this window? Built-in Help doesn't tell me much.
Rick Young
March 14, 2005 at 2:06 am
For info:
To use WIndows Authentication you'll first need to ensure that the SQL Server is set-up for it:
Right click on the SQL Server in Enterprise Manager and select the SECURITY tab and check the setting is set to "SQL SERVER AND WINDOWS".
For Windows Authentication to work the SQL Server and the PCs that want to access it have to be in the same or trusted domain.
Set-up your users via the normal Security section under the SQL Server - it's preferable to set-up a windows domain group and then set-up the Domain Group as a user under SQL Server, you then simply have to add you users to the Windows Domain Group.
The connection from the access application to the SQL Server will need to be changed so that it uses Integrated Login:
Replace the user name and password in the connect string with "Integrated Security=SSPI;"
I hope this is of some help.
Regards,
Nic Washington.
March 15, 2005 at 10:15 am
Is there ANY way around this? What about specifing the User / Pass, but it's for another domain.. ie...
SQLServer is part of domain1\sqlserver
but users login to domain2\mymachine...
Can I specify domain2\myusername and mypassword to connect some how?
March 16, 2005 at 6:58 am
I tried following Grasshopper's advice but I must be on a different version (Windows NT). When I right-click on Microsoft Sql Server I get a list of options:
Show Replication Monitor Group
About Sql Server Enterprise Manager
View - Large, Small Incon, List, Details
New Window From Here
Refresh
Export List
Help
If I double click on Microsoft Sql Server, then SQL Server Group, and then (local)(Windows NT) I get a number of folders, one of which is Security. Right-clicking on Security folder gives me nothing that helps. If I double-click on the Security folder I get: Logins, Server ROles, Linked Servers, Remote Servers.
Nothing about SQL SERVER AND WINDOWS
Is this because I'm on an NT server and his instructions are for 2000?
March 16, 2005 at 10:09 am
Tom,
You're on the right track. When Nic said to right-click on the SQL Server, he meant you should right-click on a specific instance of SQL Server. Expand Microsoft SQL Servers, expand SQL Server Group, right-click on the server name, select Properties, select Security tab.
If you have Books Online, search the index for "Logins-SQL Server" and read the overview. Also, you should probably verify with the vendor that their application will work with Windows authentication instead of SQL Server authentication.
Greg
Greg
March 16, 2005 at 12:40 pm
Thanks. I was able to find the Security tab and SQL Server And Windows is a checked option.
I'll check to see if we have Books Online and search for "Logins-SQL Server" as well as verify that the vendor's application will work with Windows authentification.
March 17, 2005 at 7:54 am
I found a helpful article on INFORMIT.COM (I'm not associated with this orgainzation but I do get their SQL Server Newsletter). The latest issue had info about how to describe and manage SQL Server security objects. The beginning of the article has a link to a simplified description of SqlServer database security:
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=35
I found it a good read.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply