|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Passing username to sql server from an appI have an ASP/ASP.NET app that is connecting to SQL Server 2000 with login 'APP'. Users are logging into the app using Windows Authentication. What is the most generic way of passing the username through to sql server? I don't really want to have to add it as a parameter to every single sp in the database. Plus figuring how to pass it through updategrams and XML template etc etc... I realise I could probably use Impersonation and delegation, but it looks to me as if I won't be able to use an app role (for reasons I won't go into - well okay - widespread use of updategrams) and I don't want to give a whole load of users logins and loads of permissions on SQL Server. Cheers, James
http://www.connectionstrings.com/
The most generic way is to create a SQL server authentication and use one of these connection strings for SQL server that includes integrated security. "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;" somewhere in your web.config In order to pass the value through to the SQL server you will have to set up your IIS to ask for windows integrated security. Here is a great article on how to do it in depth... http://dallas.sark.com/SarkBlog/cboland/archive/2005/11/28/2267.aspx Show quote "JimLad" <jamesdbi***@yahoo.co.uk> wrote in message news:1158166646.475914.119530@e3g2000cwe.googlegroups.com... > Hi, > > I have an ASP/ASP.NET app that is connecting to SQL Server 2000 with > login 'APP'. Users are logging into the app using Windows > Authentication. > > What is the most generic way of passing the username through to sql > server? I don't really want to have to add it as a parameter to every > single sp in the database. Plus figuring how to pass it through > updategrams and XML template etc etc... > > I realise I could probably use Impersonation and delegation, but it > looks to me as if I won't be able to use an app role (for reasons I > won't go into - well okay - widespread use of updategrams) and I don't > want to give a whole load of users logins and loads of permissions on > SQL Server. > > Cheers, > > James > Just for curiosity. Now if I have 100,000 online users, is it common to
create NT login and SQL login for each of them? James Show quote "Warren Brunk" wrote: > http://www.connectionstrings.com/ > > The most generic way is to create a SQL server authentication and use one of > these connection strings for SQL server that includes integrated security. > > "Driver={SQL Native > Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;" > > somewhere in your web.config > > In order to pass the value through to the SQL server you will have to set up > your IIS to ask for windows integrated security. > > Here is a great article on how to do it in depth... > http://dallas.sark.com/SarkBlog/cboland/archive/2005/11/28/2267.aspx > > -- > /* > Warren Brunk - MCITP - SQL 2005, MCDBA > www.techintsolutions.com > */ > > > "JimLad" <jamesdbi***@yahoo.co.uk> wrote in message > news:1158166646.475914.119530@e3g2000cwe.googlegroups.com... > > Hi, > > > > I have an ASP/ASP.NET app that is connecting to SQL Server 2000 with > > login 'APP'. Users are logging into the app using Windows > > Authentication. > > > > What is the most generic way of passing the username through to sql > > server? I don't really want to have to add it as a parameter to every > > single sp in the database. Plus figuring how to pass it through > > updategrams and XML template etc etc... > > > > I realise I could probably use Impersonation and delegation, but it > > looks to me as if I won't be able to use an app role (for reasons I > > won't go into - well okay - widespread use of updategrams) and I don't > > want to give a whole load of users logins and loads of permissions on > > SQL Server. > > > > Cheers, > > > > James > > > > > No it is not common.
How are the users connecting to SQL server? Through an application? I would use an app role if that is the case. I might even use forms authentication and hold the users in a table. If there were 100,000 domain users (which is just craziness) I would create an NT group and add the users to that domain. If I was hosting different DB's for those 100,000 people I would have a script that I ran upon DB creation that created the user name and password. there are really a thousand different ways to do it. If I had a little more information I could help you further. thanks, Show quote "James Ma" <Jame***@discussions.microsoft.com> wrote in message news:A5F21226-5DA9-49E4-A033-F38D2E799B05@microsoft.com... > Just for curiosity. Now if I have 100,000 online users, is it common to > create NT login and SQL login for each of them? > > James > > "Warren Brunk" wrote: > >> http://www.connectionstrings.com/ >> >> The most generic way is to create a SQL server authentication and use one >> of >> these connection strings for SQL server that includes integrated >> security. >> >> "Driver={SQL Native >> Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;" >> >> somewhere in your web.config >> >> In order to pass the value through to the SQL server you will have to set >> up >> your IIS to ask for windows integrated security. >> >> Here is a great article on how to do it in depth... >> http://dallas.sark.com/SarkBlog/cboland/archive/2005/11/28/2267.aspx >> >> -- >> /* >> Warren Brunk - MCITP - SQL 2005, MCDBA >> www.techintsolutions.com >> */ >> >> >> "JimLad" <jamesdbi***@yahoo.co.uk> wrote in message >> news:1158166646.475914.119530@e3g2000cwe.googlegroups.com... >> > Hi, >> > >> > I have an ASP/ASP.NET app that is connecting to SQL Server 2000 with >> > login 'APP'. Users are logging into the app using Windows >> > Authentication. >> > >> > What is the most generic way of passing the username through to sql >> > server? I don't really want to have to add it as a parameter to every >> > single sp in the database. Plus figuring how to pass it through >> > updategrams and XML template etc etc... >> > >> > I realise I could probably use Impersonation and delegation, but it >> > looks to me as if I won't be able to use an app role (for reasons I >> > won't go into - well okay - widespread use of updategrams) and I don't >> > want to give a whole load of users logins and loads of permissions on >> > SQL Server. >> > >> > Cheers, >> > >> > James >> > >> >> >> Similar to JimLad (refer to a previous post), if I don't like to create
100,000 logins, and I want to audit user's update to certain tables, how can I know the exact username in my audit trigger? James Show quote "Warren Brunk" wrote: > No it is not common. > How are the users connecting to SQL server? Through an application? I > would use an app role if that is the case. I might even use forms > authentication and hold the users in a table. > If there were 100,000 domain users (which is just craziness) I would create > an NT group and add the users to that domain. > If I was hosting different DB's for those 100,000 people I would have a > script that I ran upon DB creation that created the user name and password. > > there are really a thousand different ways to do it. If I had a little more > information I could help you further. > > thanks, > > > > -- > /* > Warren Brunk - MCITP - SQL 2005, MCDBA > www.techintsolutions.com > */ > > > "James Ma" <Jame***@discussions.microsoft.com> wrote in message > news:A5F21226-5DA9-49E4-A033-F38D2E799B05@microsoft.com... > > Just for curiosity. Now if I have 100,000 online users, is it common to > > create NT login and SQL login for each of them? > > > > James > > > > "Warren Brunk" wrote: > > > >> http://www.connectionstrings.com/ > >> > >> The most generic way is to create a SQL server authentication and use one > >> of > >> these connection strings for SQL server that includes integrated > >> security. > >> > >> "Driver={SQL Native > >> Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;" > >> > >> somewhere in your web.config > >> > >> In order to pass the value through to the SQL server you will have to set > >> up > >> your IIS to ask for windows integrated security. > >> > >> Here is a great article on how to do it in depth... > >> http://dallas.sark.com/SarkBlog/cboland/archive/2005/11/28/2267.aspx > >> > >> -- > >> /* > >> Warren Brunk - MCITP - SQL 2005, MCDBA > >> www.techintsolutions.com > >> */ > >> > >> > >> "JimLad" <jamesdbi***@yahoo.co.uk> wrote in message > >> news:1158166646.475914.119530@e3g2000cwe.googlegroups.com... > >> > Hi, > >> > > >> > I have an ASP/ASP.NET app that is connecting to SQL Server 2000 with > >> > login 'APP'. Users are logging into the app using Windows > >> > Authentication. > >> > > >> > What is the most generic way of passing the username through to sql > >> > server? I don't really want to have to add it as a parameter to every > >> > single sp in the database. Plus figuring how to pass it through > >> > updategrams and XML template etc etc... > >> > > >> > I realise I could probably use Impersonation and delegation, but it > >> > looks to me as if I won't be able to use an app role (for reasons I > >> > won't go into - well okay - widespread use of updategrams) and I don't > >> > want to give a whole load of users logins and loads of permissions on > >> > SQL Server. > >> > > >> > Cheers, > >> > > >> > James > >> > > >> > >> > >> > > > I can't imagine maintaining NT security accounts for that many users.
I don't see how anything other than a database table with account information would work. Do you have 100,000 users? How are you approaching it? Show quote "James Ma" <Jame***@discussions.microsoft.com> wrote in message news:A5F21226-5DA9-49E4-A033-F38D2E799B05@microsoft.com... > Just for curiosity. Now if I have 100,000 online users, is it common to > create NT login and SQL login for each of them? > > James > > "Warren Brunk" wrote: > > > http://www.connectionstrings.com/ > > > > The most generic way is to create a SQL server authentication and use one of > > these connection strings for SQL server that includes integrated security. > > > > "Driver={SQL Native > > Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;" > > > > somewhere in your web.config > > > > In order to pass the value through to the SQL server you will have to set up > > your IIS to ask for windows integrated security. > > > > Here is a great article on how to do it in depth... > > http://dallas.sark.com/SarkBlog/cboland/archive/2005/11/28/2267.aspx > > > > -- > > /* > > Warren Brunk - MCITP - SQL 2005, MCDBA > > www.techintsolutions.com > > */ > > > > > > "JimLad" <jamesdbi***@yahoo.co.uk> wrote in message > > news:1158166646.475914.119530@e3g2000cwe.googlegroups.com... > > > Hi, > > > > > > I have an ASP/ASP.NET app that is connecting to SQL Server 2000 with > > > login 'APP'. Users are logging into the app using Windows > > > Authentication. > > > > > > What is the most generic way of passing the username through to sql > > > server? I don't really want to have to add it as a parameter to every > > > single sp in the database. Plus figuring how to pass it through > > > updategrams and XML template etc etc... > > > > > > I realise I could probably use Impersonation and delegation, but it > > > looks to me as if I won't be able to use an app role (for reasons I > > > won't go into - well okay - widespread use of updategrams) and I don't > > > want to give a whole load of users logins and loads of permissions on > > > SQL Server. > > > > > > Cheers, > > > > > > James > > > > > > > > > I would use some sort of forms authentication and use a query to return user
information from a database table. Show quote "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message news:eaLovD21GHA.1588@TK2MSFTNGP02.phx.gbl... >I can't imagine maintaining NT security accounts for that many users. > > I don't see how anything other than a database table with account > information would work. > > Do you have 100,000 users? How are you approaching it? > > "James Ma" <Jame***@discussions.microsoft.com> wrote in message > news:A5F21226-5DA9-49E4-A033-F38D2E799B05@microsoft.com... >> Just for curiosity. Now if I have 100,000 online users, is it common to >> create NT login and SQL login for each of them? >> >> James >> >> "Warren Brunk" wrote: >> >> > http://www.connectionstrings.com/ >> > >> > The most generic way is to create a SQL server authentication and use > one of >> > these connection strings for SQL server that includes integrated > security. >> > >> > "Driver={SQL Native >> > Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;" >> > >> > somewhere in your web.config >> > >> > In order to pass the value through to the SQL server you will have to > set up >> > your IIS to ask for windows integrated security. >> > >> > Here is a great article on how to do it in depth... >> > http://dallas.sark.com/SarkBlog/cboland/archive/2005/11/28/2267.aspx >> > >> > -- >> > /* >> > Warren Brunk - MCITP - SQL 2005, MCDBA >> > www.techintsolutions.com >> > */ >> > >> > >> > "JimLad" <jamesdbi***@yahoo.co.uk> wrote in message >> > news:1158166646.475914.119530@e3g2000cwe.googlegroups.com... >> > > Hi, >> > > >> > > I have an ASP/ASP.NET app that is connecting to SQL Server 2000 with >> > > login 'APP'. Users are logging into the app using Windows >> > > Authentication. >> > > >> > > What is the most generic way of passing the username through to sql >> > > server? I don't really want to have to add it as a parameter to every >> > > single sp in the database. Plus figuring how to pass it through >> > > updategrams and XML template etc etc... >> > > >> > > I realise I could probably use Impersonation and delegation, but it >> > > looks to me as if I won't be able to use an app role (for reasons I >> > > won't go into - well okay - widespread use of updategrams) and I >> > > don't >> > > want to give a whole load of users logins and loads of permissions on >> > > SQL Server. >> > > >> > > Cheers, >> > > >> > > James >> > > >> > >> > >> > > >
http://msdn2.microsoft.com/en-us/library/bsz5788z.aspx
another great article from Microsoft... Show quote "JimLad" <jamesdbi***@yahoo.co.uk> wrote in message news:1158166646.475914.119530@e3g2000cwe.googlegroups.com... > Hi, > > I have an ASP/ASP.NET app that is connecting to SQL Server 2000 with > login 'APP'. Users are logging into the app using Windows > Authentication. > > What is the most generic way of passing the username through to sql > server? I don't really want to have to add it as a parameter to every > single sp in the database. Plus figuring how to pass it through > updategrams and XML template etc etc... > > I realise I could probably use Impersonation and delegation, but it > looks to me as if I won't be able to use an app role (for reasons I > won't go into - well okay - widespread use of updategrams) and I don't > want to give a whole load of users logins and loads of permissions on > SQL Server. > > Cheers, > > James > For large scale web app with many users, normally it must use only several
special accounts to connect to db, so you have to pass app-wise user context by yourself. I see "Session Context Information" in BOL is pretty hopeful, or consider to use the temp table trick I suggested before. James Show quote "JimLad" wrote: > Hi, > > I have an ASP/ASP.NET app that is connecting to SQL Server 2000 with > login 'APP'. Users are logging into the app using Windows > Authentication. > > What is the most generic way of passing the username through to sql > server? I don't really want to have to add it as a parameter to every > single sp in the database. Plus figuring how to pass it through > updategrams and XML template etc etc... > > I realise I could probably use Impersonation and delegation, but it > looks to me as if I won't be able to use an app role (for reasons I > won't go into - well okay - widespread use of updategrams) and I don't > want to give a whole load of users logins and loads of permissions on > SQL Server. > > Cheers, > > James > >  |
|||||||||||||||||||||||
Other interesting topics