March 13, 2012 at 7:22 am
Hi
Is it possible to have a username and log in specified in a connection string? I would like to create Membership Provider for .net that uses SQL's Roles and Users as it's datasource and every user will connect using there own username in the connection string. But is it possible at all to have only 1 log in for the SQL instance and all the users in the database use this login to authenticate? or is there a way for me to add extra information to the connection string and the SQL Auditing can use that data when auditing and that I can access that data from stored procedures.
Thanks in advanced 😀
The Fastest Methods aren't always the Quickest Methods
March 13, 2012 at 7:55 am
http://www.connectionstrings.com/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 8:22 am
thank you but nothing there helped me with this query, I know the different ways to make a connection string but was wondering if there is a way to add meta data to it that can be used for SQL Auditing or in stored procedures
The Fastest Methods aren't always the Quickest Methods
March 13, 2012 at 8:27 am
What exactly are you looking for? What info could you pass in a connection string that would help with auditing?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 8:35 am
If I could use a basic connection string and then maybe pass an ID of the user that is making the changes then when we look at the audits we know which user in the application made the changes.
The Fastest Methods aren't always the Quickest Methods
March 13, 2012 at 9:18 am
You could do something like this...but I think this really belongs in front end code.
You could add "Application Name" to your connection string and then you can query that.
Server=(local);Database=xxx;Uid=xxx;Pwd=xxx;Application Name=MyNameHere
Now in the application you would have to first authenticate your user. Once connected you could create a new connection and replace MyNameHere with the user name or some other sort of data.
You can then query this data from sys.sysprocesses where with something like this.
select spid, hostname, program_name, convert(sysname, rtrim(loginame)) as loginname
from sys.sysprocesses
where spid = @@SPID
Does that get you pointed in the direction you were looking for?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 9:22 am
Option 1: Use Windows Authentication.
You're describing the whole premise behind Windows Authentication! ...why are we using SQL Logins? Is this for a public-facing web app or an app with many concurrent users?
----------
Option 2: piggy-back on the Application Name property in your connection string
If you will have the ability to manipulate the connection string's properties before connecting -AND- you are not using connection pooling you could use the Application Name property of the connection string (max 128 bytes, at least according to sys.dm_exec_sessions.program_name) to get information into the data layer. You could then access that information within any sql object this way:
SELECT program_name
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
Look for "Application Name" > All SQL Server SqlConnection properties
If this app will have many concurrent users you would not want to forego connection pooling.
----------
Option 3: use SQL Server session context info
Another option is to have your data access layer initialize the context info for each session using SET CONTEXT_INFO as the first command after it connects.
Using Session Context Information
This can be used to uniquely identify the person behind the connection, while still allowing for a common login to be used to authenticate and authorize the database activity.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2012 at 9:24 am
Sean, you beat me to it on the Application Name post by 4 minutes! Tsk tsk on the use of sys.sysprocesses here 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2012 at 9:26 am
opc.three (3/13/2012)
Sean, you beat me to it on the Application Name post by 4 minutes! Tsk tsk on the use of sys.sysprocesses here 😉
True!! :blush: Old habits die hard.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 11:59 am
It is for a client of ours, the system isn't public facing but all the users will not always login from the same machine and all there clients that use it are from different companies. The Application Name is exactly what I will need for what I am trying to achieve =D.
Thank you 😀
The Fastest Methods aren't always the Quickest Methods
March 13, 2012 at 12:32 pm
Sounds good, happy to assist! Just to drive the point home about connection pooling...any difference between two connection strings will cause them to be viewed as different connections by your connection pool. In other words by leveraging the Application Name property to store information specific to a user you're forcing at minimum one connection into the pool for each user and negating some of the benefits of connection pooling. If you have a high number of users this can become a bottleneck for your applciation.
As an example, a connection with this connection string cannot be issued to anyone but Rick:
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;Application Name=MyApp-Rick;
Meaning when Susie logs into the application from another computer needing something from the database using the connection string below, even if Rick is not actively using the connection his last action generated in the connection pool, a new connection will be created and added to the pool just for Susie:
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;Application Name=MyApp-Susie;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2012 at 12:35 am
Thank you for this, we will keep this in mind when implementing 😀
The Fastest Methods aren't always the Quickest Methods
March 28, 2012 at 4:00 am
Hi the best way to achieve this is to ask your Exchange Administrators to create a Group Admini Account on AD and add all the users that you need to be a member of that group account. After that you may add the Group Account to SQL.
Now everyuser that logs in to SQL will will be granted access if he is a memeber of the group. I am not sure if this answer's your Question.
Also just make sure ur .Net is configured to use intergrated Authentication or SSI.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply