June 24, 2008 at 10:31 am
Hello,
I have a VB application connecting to SQL Server database using SQL Server Authentication. I'd like to use Windows security instead. If I replace 'Uid=myUsername;Pwd=myPassword;' in connection string to 'Integrated Security=SSPI' it will allow users connect to SQl server with windows account as long as this account has access in SQL.
Is there a way to connect to SQL server from application, prompting for Windows login/password, regardless of who/what windows account is logged in at the moment on the user's machine? In other words, if user A logged in on machine M with his Windows account, and user B wants to use my application on the same machine while user A is logged to Windows Network, how to tell SQL server to use user's B Windows Authentication (windows username/password)?
thanks you
July 8, 2008 at 12:14 pm
You can add a layer (pop up a login window) in your VB code to prompt your users to input their logins and passwords. How does it work?
July 8, 2008 at 12:20 pm
You can't do this, because the drivers for OLEDB/ODBC will use the logged on credentials.
Most OS's now allow a "switch user", which logs in a 2nd person.
July 8, 2008 at 1:56 pm
liya.b (6/24/2008)
Hello,I have a VB application connecting to SQL Server database using SQL Server Authentication. I'd like to use Windows security instead. If I replace 'Uid=myUsername;Pwd=myPassword;' in connection string to 'Integrated Security=SSPI' it will allow users connect to SQl server with windows account as long as this account has access in SQL.
Is there a way to connect to SQL server from application, prompting for Windows login/password, regardless of who/what windows account is logged in at the moment on the user's machine? In other words, if user A logged in on machine M with his Windows account, and user B wants to use my application on the same machine while user A is logged to Windows Network, how to tell SQL server to use user's B Windows Authentication (windows username/password)?
thanks you
Is the machine a shared system like a kiosk? If not, Steve's suggestion of "switch user" is the way to go. If it is a kiosk, you can have that machine auto log on as a particular user and then make the connection as that auto logged on user.
K. Brian Kelley
@kbriankelley
July 8, 2008 at 3:44 pm
Steve Jones - Editor (7/8/2008)
You can't do this, because the drivers for OLEDB/ODBC will use the logged on credentials.Most OS's now allow a "switch user", which logs in a 2nd person.
We did the similar thing before. In VB user interface, we could pop up a login window to request login info from the user. Based on this login info, a connection string is built.
July 8, 2008 at 4:46 pm
A possible approach is to open your app with a simple form with a window for entering the users SQL Server Login Name - which may or may not be the same as their Windows login name.
Now comes the fun part .... assume you are using VB.Net so start reading here from BOL
UserCollection Class
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/f28756b3-373f-92e8-3306-6a9b138540cf.htm
The UserCollection class represents a collection of User objects that represent all the database users defined on a database.
Namespace: Microsoft.SqlServer.Management.Smo
Assembly: Microsoft.SqlServer.Smo (in microsoft.sqlserver.smo.dll)
Syntax
Visual Basic
_
Public NotInheritable Class UserCollection
Inherits SimpleObjectCollectionBase
A UserCollection object can be used to gain access to a set of database users. You can use the Item property to return a specific User object.
This namespace, class, or member is supported only in version 2.0 of the Microsoft .NET Framework.
Inheritance Hierarchy
System.Object
Microsoft.SqlServer.Management.Smo.AbstractCollectionBase
Microsoft.SqlServer.Management.Smo.SmoCollectionBase
Microsoft.SqlServer.Management.Smo.SortedListCollectionBase
Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase
Microsoft.SqlServer.Management
For example from How to: Enumerate Logins and Associated Users in Visual Basic .NET
(This assumes that the normal loged in user is defined in the particular database) Sample code copied from BOL:
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Iterate through each database and display.
Dim db As Database
For Each db In srv.Databases
Console.WriteLine("============================================")
Console.WriteLine("Login Mappings for the database: " + db.Name)
Console.WriteLine(" ")
'Run the EnumLoginMappings method and return details of database user-login mappings to a DataTable object variable.
Dim d As DataTable
d = db.EnumLoginMappings
'Display the mapping information.
Dim r As DataRow
Dim c As DataColumn
For Each r In d.Rows
For Each c In r.Table.Columns
Console.WriteLine(c.ColumnName + " = " + r(c))
Next
Console.WriteLine(" ")
Next
Next
Your job is to check each item
How to: Reference an Object by Using a Collection in Visual Basic .NET
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9/html/2055415e-1f9c-4e53-9d61-e0d339db9bd4.htm
Or get away from the dot Net gibberish and issue the following T-SQL
SELECT pincipal_id from sys.server_principals WHERE name LIKE '% users input of login name %' if it returns a positive value great than 0. The user has is authorized to login to the server.
Then your next worry is does the user have the authorization to see what the app displays, update or delete what the app can update or delete.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply