Attach remote DB file: login

Showing posts with label login. Show all posts

Sunday, March 25, 2012

authorization and permissions

Hi All,
I'm development a system, and I have doubts about login (authorization and
permissions)
This system need be very secure.
The systems have clients that connect in a web service and this web service
connects in SQL Server.
My doubt is:
What is the best:
Logins here is: Name and password(hash) and what each user can do.
Store logins in one table in my database and always WebServise use SA
account to manipulate data base?
Use SQL logins for each users?
Use Windows logins for each users?
ThanksFirst of all, abstraction is the easiest to manage and administrate. In all
regards, use Windows Authentication whenever possible. If this is public
facing, in all likelihood, you will not have Windows Domain Accounts
available for use.
In this case, and in cases where the applicaiton needs to control the
security of the users (that is, keep explicit user identification and
permissions within the application database), it would be better to use a
single Windows Authenticated account to manipulate database calls.
Also, you should not have the Web Services directly manipulate the database.
In stead, you should have application layer services resident on another
server from you Web Server and have the web call these services. The
application tier should then use this single application, Windows
Authenticated account control the connections and calls to the Data Services
tier.
The interfaces exposed from the DBMS should all be done through stored
procedures and ad-hoc requests for reporting and administrative services
should be exposed through Views. No direct base table access should be
granted outside of the development/application support staff, and even then,
restricted to DML activities alone. The DBA should be the only user
authorized to make system level changes, including DDL, and only through a
formalized Change Control process.
Sincerely,
Anthony Thomas
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:O3KN$thaFHA.3048@.TK2MSFTNGP12.phx.gbl...
Hi All,
I'm development a system, and I have doubts about login (authorization and
permissions)
This system need be very secure.
The systems have clients that connect in a web service and this web service
connects in SQL Server.
My doubt is:
What is the best:
Logins here is: Name and password(hash) and what each user can do.
Store logins in one table in my database and always WebServise use SA
account to manipulate data base?
Use SQL logins for each users?
Use Windows logins for each users?
Thanks|||This is a multi-part message in MIME format.
--090003030806010808040200
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
The most secure option is to force all users to have their own login and
make them all trusted logins. Of course, there are typically practical
problems with that approach from web apps as the user that hits the web
server needs to be authenticated against AD for the trusted SQL login
from the web server to work properly and for externally accessible web
apps authenticating the users against AD is often not possible. But
purely from a SQL perspective trusted logins are the most secure method
of connecting.
Storing passwords in your DB is never a good idea if solid security is
what you're aiming for. Storing localised application permissions in
some user table in your DB is fine as long as you have the table locked
down so if a user hacks in somehow they can't "tweak" the permissions
table to change their level of permissions within the app. As far as
SQL permissions go, each different "role" within your app (eg. clerk,
manager, administrator, etc.) should have a corresponding role within
the DB (see sp_addrole and sp_addrolemember in BOL) with the necessary
SQL permissions assigned to the DB roles.
It's best not to use the 'sa' login for anything. In fact, if you have
Windows Authentication only then you cannot login with 'sa' because it's
a SQL login (not a trusted login).
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
ReTF wrote:
>Hi All,
>
>I'm development a system, and I have doubts about login (authorization and
>permissions)
>This system need be very secure.
>
>The systems have clients that connect in a web service and this web service
>connects in SQL Server.
>
>My doubt is:
>
>What is the best:
>
>Logins here is: Name and password(hash) and what each user can do.
>
>Store logins in one table in my database and always WebServise use SA
>account to manipulate data base?
>
>Use SQL logins for each users?
>
>Use Windows logins for each users?
>
>Thanks
>
>
--090003030806010808040200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>The most secure option is to force all users to have their own
login and make them all trusted logins. Of course, there are typically
practical problems with that approach from web apps as the user that
hits the web server needs to be authenticated against AD for the
trusted SQL login from the web server to work properly and for
externally accessible web apps authenticating the users against AD is
often not possible. But purely from a SQL perspective trusted logins
are the most secure method of connecting. 
 
Storing passwords in your DB is never a good idea if solid security is
what you're aiming for. Storing localised application permissions in
some user table in your DB is fine as long as you have the table locked
down so if a user hacks in somehow they can't "tweak" the permissions
table to change their level of permissions within the app. As far as
SQL permissions go, each different "role" within your app (eg. clerk,
manager, administrator, etc.) should have a corresponding role within
the DB (see sp_addrole and sp_addrolemember in BOL) with the necessary
SQL permissions assigned to the DB roles. 
 
It's best not to use the 'sa' login for anything. In fact, if you have
Windows Authentication only then you cannot login with 'sa' because
it's a SQL login (not a trusted login). 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
ReTF wrote:
<blockquote cite="midO3KN$thaFHA.3048@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">Hi All,
I'm development a system, and I have doubts about login (authorization and
permissions)
This system need be very secure.
The systems have clients that connect in a web service and this web service
connects in SQL Server.
My doubt is:
What is the best:
Logins here is: Name and password(hash) and what each user can do.
Store logins in one table in my database and always WebServise use SA
account to manipulate data base?
Use SQL logins for each users?
Use Windows logins for each users?
Thanks
</pre>
</blockquote>
</body>
</html>
--090003030806010808040200--

authorization and permissions

authorization

how do you alter authorization to set the database owner to a valid login? and simultaneously how do you add the database diagram support objects in sql server 2005 express.

thanksThere's two ways to change the owner to a valid login:

1) Using the management UI
a) Right click on the database in Object Explorer, select properties
b) in the properties dialog, switch to the "Files" page
c) Enter the name of a login in the "Owner" edit box (or click the "..." button to browse for a login)
d) click OK

2) Using T-SQL execute the following:

ALTER AUTHORIZATION ON DATABASE::{database_name} TO {principal_name}
To install the database diagram support objects:

1) Launch Management Studio and log in as an administrator or as dbo in the database.
2) Make sure the database compatibility level is set to SQL Server 2005. You can set this in the database properties dialog on the Options page.
3) In Object Explorer, right click on the "Database Diagrams" folder for the database and select "Install Diagram Support". Click Yes in the prompt to install support objects that is displayed.

If you need to, you can set the database compatibility to a backwards compatibility mode after the diagram support objects are installed and you'll still be able to work with diagrams.

author of package affects login?

This is a very frustrating problem.

Let me break it down for the reader.

I have an agent service account that works fine - the account that jobs run under.

I know it works fine because this is the same account all my SQL Backup jobs run under - without a hitch.

I also have a valid sql server account that I use for connection string logins when building packages -

that account works fine. I know it works fine because I can login to the SQL Server Manager with this account.

So, I build a package with the sql server account and password for the OLE DB connection manager in a package. All the package does is a simple query on a table and outputs to a flat file.

I create a job, with 'sa' as the owner. in step 1 I select SSIS package, run as SQL Agent Service Account, package source is file system and I point to the package. The package itself uses the sql server login account to execute.

If I run the package it works fine. I close the solution, someone else comes along and opens the solution - THEY CHANGE ABSOLUTELY NOTHING ABOUT THE LOGIN OR SQL AGENT SERVICE ACCOUNT, but apparently just the fact that they opened the solution and looked at the package, breaks the login. If they try to run the package, they get a login failure, whereas I did not. After they close it, it breaks for me as well. If they open the solution, open the connection manager, re-enter the sql login's password, the package works fine for them, until I come along and open the solution, then it is broken again.

This makes no sense to me. Why would an 'author' opening a solution impact the connection manager when ABOLUTELY NOTHING ABOUT THE CONNECTION MANAGER WAS CHANGED?!

Seems like a bug to me. Anyone seen something like this?

Thanks in advance.

randy

Read up on the package ProtectionLevel property in this forum and in Books-Online.

I'd bet the package is set to EncryptSensitiveWithUserKey, which would prevent others from being able to run the package. You can try EncryptSensitiveWithPassword, but then all developers would need to know the package's password and would be required to enter it to open the package and be able to use it. Either way, Microsoft did not want to be responsible for package security and made sure that the packages are secure.

You could also try DontSaveSensitive, but then you'd have to use a package configuration file to pass in the passwords to the connection managers. It's a touch cumbersome, but in the end it's worth the extra security.|||

Thanks very much. I'll take a look.

Regards

|||

Randyvol,

What you described in your first post is just the normal behavior of SSIS. SSIS does not save sensitive information like connection manager's passwords unless you use an encrypted protection level in your package. If the protection level of the package is set to DonSaveSensitive; then you have to provide the connection credentials every time you open the package. That is what seems to be happening.

As a good practice you can use package configuration to set the connection string of connection managers at run time; that way the package will always get the connection credential when is executed. That will not change the behavior of the package when editing it.

Authentification problem with SQL 2k, IIS and Linked Server

Hello,

I have successfully created an Exchange 2000 linked server in Microsoft SQL server 2k. When I use my NT login, I can query it.

I'm now developping a website (ASP.NET in IIS 5). I have successfully created a web page querying my database. But now, I would like to query my linked server via my SQL server. I have try various authentification settings but I always got this error : OLE DB provider 'Exoledb.DataSource.1' reported an error. Authentication failed.

I know it is because the IIS process don't have any rights on the Exchange server but I would like to know what is the way to get around this without given rights to the IIS process (and creating huge security holes).

Thank a lot

Felix Pageau
fpageau@.str.cadid you try distributed queries ?

for example :
INSERT INTO [Database1name].[Owner].[Table]
SELECT *
FROM OPENQUERY ([LinkedServerName], SELECT Database2name.Table.* FROM Database2Name.Table)|||Hello,

this is exactly what I'm trying to do... I would like to know if it is possible to "impersonate" an Exchange Linked Server (with a user defined in the domain...) ?

Thank|||Review information from this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;285833) to go with Linked server authentication.|||Hello,
this is working fine with access but Exchange is using Active Directory login... Those example use plain text login & password. I have already try this way.

Thank a lot

Felix Pageau

Authentication problems

I am using form authentication to login into report service. When I execute
a report, the following error is displayed:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'DAISYS'.
(rsErrorOpeningConnection) Get Online Help
Unable to load DLL (oci.dll).
But if I use window authentication, it works fine.
Please help !!!Can someone help me ?
"May Liu" wrote:
> I am using form authentication to login into report service. When I execute
> a report, the following error is displayed:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'DAISYS'.
> (rsErrorOpeningConnection) Get Online Help
> Unable to load DLL (oci.dll).
> But if I use window authentication, it works fine.
> Please help !!!|||This sounds like a file permission issue with the installation of the Oracle
client software. The ASP worker process is unable to load OCI.dll and other
configuration settings which are stored in the Oracle client installation
directory.
For instance, the Oracle 9.2 client is typically installed at:
C:\oracle\ora92. When you use Windows authentication, it seems like the
users executing reports have "Read & Execute" permissions at least on
\oracle\ora92\bin and \oracle\ora92\network\admin directories - and
therefore the ASP.NET work process can access and load the Oracle client
software (dlls and configuration fiiles) from there.
When you use Forms authentication, most likely the ASP.NET worker process
will run under a user account that does *not* have explicit Read & Execute
permissions on the directories of the Oracle client software. Make sure
these rights are explicitly granted to all files (child objects) in those
directories (on Win2003 on the directory security tab you have to click on
the Advanced button and in the new popup window you have to select "Replace
permission entries on all child objects ..." and click OK).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:823537DA-85E8-4C71-8F0D-6CFA0EAE4182@.microsoft.com...
> Can someone help me ?
> "May Liu" wrote:
> > I am using form authentication to login into report service. When I
execute
> > a report, the following error is displayed:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
Get
> > Online Help
> > Cannot create a connection to data source 'DAISYS'.
> > (rsErrorOpeningConnection) Get Online Help
> > Unable to load DLL (oci.dll).
> >
> > But if I use window authentication, it works fine.
> > Please help !!!|||It works. Thanks a lot !!!
"Robert Bruckner [MSFT]" wrote:
> This sounds like a file permission issue with the installation of the Oracle
> client software. The ASP worker process is unable to load OCI.dll and other
> configuration settings which are stored in the Oracle client installation
> directory.
> For instance, the Oracle 9.2 client is typically installed at:
> C:\oracle\ora92. When you use Windows authentication, it seems like the
> users executing reports have "Read & Execute" permissions at least on
> \oracle\ora92\bin and \oracle\ora92\network\admin directories - and
> therefore the ASP.NET work process can access and load the Oracle client
> software (dlls and configuration fiiles) from there.
> When you use Forms authentication, most likely the ASP.NET worker process
> will run under a user account that does *not* have explicit Read & Execute
> permissions on the directories of the Oracle client software. Make sure
> these rights are explicitly granted to all files (child objects) in those
> directories (on Win2003 on the directory security tab you have to click on
> the Advanced button and in the new popup window you have to select "Replace
> permission entries on all child objects ..." and click OK).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:823537DA-85E8-4C71-8F0D-6CFA0EAE4182@.microsoft.com...
> > Can someone help me ?
> >
> > "May Liu" wrote:
> >
> > > I am using form authentication to login into report service. When I
> execute
> > > a report, the following error is displayed:
> > >
> > > An error has occurred during report processing. (rsProcessingAborted)
> Get
> > > Online Help
> > > Cannot create a connection to data source 'DAISYS'.
> > > (rsErrorOpeningConnection) Get Online Help
> > > Unable to load DLL (oci.dll).
> > >
> > > But if I use window authentication, it works fine.
> > > Please help !!!
>
>sql

Thursday, March 22, 2012

Authentication over the internet

I am building an asp.net app that will use reporting services to show reports within the application. Users login to the application and when they need to see a report I use web services to render the report. The asp.net app and reporting services are on the same windows 2003 server (not using active directory).
Because reporting services uses Windows authentication and does not allow anonymous access, I have created a windows account (called "RSUser") that has access to my reports. When the user runs a report, I pass in the credentials for this windows account like this...
rs.Credentials = New System.Net.NetworkCredential("RSUser", "password", "domain")
This all works, and the report renders using the permissions from RSUser. The problem is that all the reports use treeviews for drill-down (and some use drill-through). When you expand a drill down you are prompted for a windows login. I think this is because this postback is now coming from the client PC, instead if from the asp.net app (i.e. on the server), and so reporting services needs to anthenticate this new user.
The only solution that I have found for this is developing a security extension for reporting services...
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
... but this seems like overkill and a very complicated process, and Microsoft says in the article that this is not fully tested and should not be used in a production environment (but that where I need it for).
Does anyone have a solution ?
Craig HBJust a thought: Have you tried to setup a individual Application pool that works with your RSUser Account?
"Craig HB" wrote:
> I am building an asp.net app that will use reporting services to show reports within the application. Users login to the application and when they need to see a report I use web services to render the report. The asp.net app and reporting services are on the same windows 2003 server (not using active directory).
> Because reporting services uses Windows authentication and does not allow anonymous access, I have created a windows account (called "RSUser") that has access to my reports. When the user runs a report, I pass in the credentials for this windows account like this...
> rs.Credentials = New System.Net.NetworkCredential("RSUser", "password", "domain")
> This all works, and the report renders using the permissions from RSUser. The problem is that all the reports use treeviews for drill-down (and some use drill-through). When you expand a drill down you are prompted for a windows login. I think this is because this postback is now coming from the client PC, instead if from the asp.net app (i.e. on the server), and so reporting services needs to anthenticate this new user.
> The only solution that I have found for this is developing a security extension for reporting services...
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> ... but this seems like overkill and a very complicated process, and Microsoft says in the article that this is not fully tested and should not be used in a production environment (but that where I need it for).
> Does anyone have a solution ?
> Craig HB|||Craig,
You are right. You get prompted because the drilldown and drillthough
interactive features require URL acccess and request goes out on the client
side of the application.
In a nutshell, if your reports have interactive features you need to go for
URL access. For Internet-oriented apps this means writing a custom security
extension. It is not that involved to write and I have deployed an
application that uses a custom security extension in a production
environment. There are some gotchas to avoid but in general my experience
writing custom security extensions have been positive and you will learn a
lot about how RS handles authentication and authorization.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Gash" <Gash@.discussions.microsoft.com> wrote in message
news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> Just a thought: Have you tried to setup a individual Application pool that
works with your RSUser Account?
> "Craig HB" wrote:
> > I am building an asp.net app that will use reporting services to show
reports within the application. Users login to the application and when they
need to see a report I use web services to render the report. The asp.net
app and reporting services are on the same windows 2003 server (not using
active directory).
> >
> > Because reporting services uses Windows authentication and does not
allow anonymous access, I have created a windows account (called "RSUser")
that has access to my reports. When the user runs a report, I pass in the
credentials for this windows account like this...
> >
> > rs.Credentials = New System.Net.NetworkCredential("RSUser", "password",
"domain")
> >
> > This all works, and the report renders using the permissions from
RSUser. The problem is that all the reports use treeviews for drill-down
(and some use drill-through). When you expand a drill down you are prompted
for a windows login. I think this is because this postback is now coming
from the client PC, instead if from the asp.net app (i.e. on the server),
and so reporting services needs to anthenticate this new user.
> >
> > The only solution that I have found for this is developing a security
extension for reporting services...
> >
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> >
> > ... but this seems like overkill and a very complicated process, and
Microsoft says in the article that this is not fully tested and should not
be used in a production environment (but that where I need it for).
> >
> > Does anyone have a solution ?
> >
> > Craig HB|||Start here
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:3A2F7D63-C267-4CED-A5CC-4B42186B98B6@.microsoft.com...
> Is there any sample code for writing a custom security extension? I have
> already validated the user and I want to provide url access to the report
> server.
> "Teo" wrote:
> > Craig,
> >
> > You are right. You get prompted because the drilldown and drillthough
> > interactive features require URL acccess and request goes out on the
client
> > side of the application.
> >
> > In a nutshell, if your reports have interactive features you need to go
for
> > URL access. For Internet-oriented apps this means writing a custom
security
> > extension. It is not that involved to write and I have deployed an
> > application that uses a custom security extension in a production
> > environment. There are some gotchas to avoid but in general my
experience
> > writing custom security extensions have been positive and you will learn
a
> > lot about how RS handles authentication and authorization.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > "Gash" <Gash@.discussions.microsoft.com> wrote in message
> > news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > > Just a thought: Have you tried to setup a individual Application pool
that
> > works with your RSUser Account?
> > >
> > > "Craig HB" wrote:
> > >
> > > > I am building an asp.net app that will use reporting services to
show
> > reports within the application. Users login to the application and when
they
> > need to see a report I use web services to render the report. The
asp.net
> > app and reporting services are on the same windows 2003 server (not
using
> > active directory).
> > > >
> > > > Because reporting services uses Windows authentication and does not
> > allow anonymous access, I have created a windows account (called
"RSUser")
> > that has access to my reports. When the user runs a report, I pass in
the
> > credentials for this windows account like this...
> > > >
> > > > rs.Credentials = New System.Net.NetworkCredential("RSUser",
"password",
> > "domain")
> > > >
> > > > This all works, and the report renders using the permissions from
> > RSUser. The problem is that all the reports use treeviews for drill-down
> > (and some use drill-through). When you expand a drill down you are
prompted
> > for a windows login. I think this is because this postback is now coming
> > from the client PC, instead if from the asp.net app (i.e. on the
server),
> > and so reporting services needs to anthenticate this new user.
> > > >
> > > > The only solution that I have found for this is developing a
security
> > extension for reporting services...
> > > >
> > > >
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > > >
> > > > ... but this seems like overkill and a very complicated process, and
> > Microsoft says in the article that this is not fully tested and should
not
> > be used in a production environment (but that where I need it for).
> > > >
> > > > Does anyone have a solution ?
> > > >
> > > > Craig HB
> >
> >
> >|||I have seen this article and it is good if I wanted to build a standalone
application to allow access to the report server. However, i have an existing
application with forms authentication in which I want to embed url access to
the report server. I was hoping that there would be code samples or an
article for this particular issue. I don't need to present another form to
the user to capture credentials. Can i use my existing forms authentication
ticket or do I need to create a new one. Do I call the LogonUser webservice
to create a cookie for a user that has been created on the report manager. It
seems like there are a lot of people trying to solve the same problem, but
not too many examples.
"Teo Lachev" wrote:
> Start here
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> news:3A2F7D63-C267-4CED-A5CC-4B42186B98B6@.microsoft.com...
> > Is there any sample code for writing a custom security extension? I have
> > already validated the user and I want to provide url access to the report
> > server.
> >
> > "Teo" wrote:
> >
> > > Craig,
> > >
> > > You are right. You get prompted because the drilldown and drillthough
> > > interactive features require URL acccess and request goes out on the
> client
> > > side of the application.
> > >
> > > In a nutshell, if your reports have interactive features you need to go
> for
> > > URL access. For Internet-oriented apps this means writing a custom
> security
> > > extension. It is not that involved to write and I have deployed an
> > > application that uses a custom security extension in a production
> > > environment. There are some gotchas to avoid but in general my
> experience
> > > writing custom security extensions have been positive and you will learn
> a
> > > lot about how RS handles authentication and authorization.
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > http://www.prologika.com
> > >
> > >
> > > "Gash" <Gash@.discussions.microsoft.com> wrote in message
> > > news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > > > Just a thought: Have you tried to setup a individual Application pool
> that
> > > works with your RSUser Account?
> > > >
> > > > "Craig HB" wrote:
> > > >
> > > > > I am building an asp.net app that will use reporting services to
> show
> > > reports within the application. Users login to the application and when
> they
> > > need to see a report I use web services to render the report. The
> asp.net
> > > app and reporting services are on the same windows 2003 server (not
> using
> > > active directory).
> > > > >
> > > > > Because reporting services uses Windows authentication and does not
> > > allow anonymous access, I have created a windows account (called
> "RSUser")
> > > that has access to my reports. When the user runs a report, I pass in
> the
> > > credentials for this windows account like this...
> > > > >
> > > > > rs.Credentials = New System.Net.NetworkCredential("RSUser",
> "password",
> > > "domain")
> > > > >
> > > > > This all works, and the report renders using the permissions from
> > > RSUser. The problem is that all the reports use treeviews for drill-down
> > > (and some use drill-through). When you expand a drill down you are
> prompted
> > > for a windows login. I think this is because this postback is now coming
> > > from the client PC, instead if from the asp.net app (i.e. on the
> server),
> > > and so reporting services needs to anthenticate this new user.
> > > > >
> > > > > The only solution that I have found for this is developing a
> security
> > > extension for reporting services...
> > > > >
> > > > >
> > >
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > > > >
> > > > > ... but this seems like overkill and a very complicated process, and
> > > Microsoft says in the article that this is not fully tested and should
> not
> > > be used in a production environment (but that where I need it for).
> > > > >
> > > > > Does anyone have a solution ?
> > > > >
> > > > > Craig HB
> > >
> > >
> > >
>
>|||> Can i use my existing forms authentication
> ticket or do I need to create a new one.
No, you cannot use your app Forms Authentication ticket and you don't have
to have another logon form. Instead, your application needs to call the RS
LogonUser SOAP API once it authenticates the user. You will end up with two
authentication tickets (cookies) but this shouldn't be too much of an issue.
The MS article should be good enough to address you scenario. You just need
to understand how RS Forms Authentication works by debugging the extension.
I have a two-part article in the works for a magazine about Forms
Authentication. Unfortunately, judging by the editors speed, it won't make
it before the end of the year. Meanwhile, you can check the other threads
on this topic. It's been discussed many times.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:3F687097-1790-4FF9-B8CB-0A163BF3074C@.microsoft.com...
> I have seen this article and it is good if I wanted to build a standalone
> application to allow access to the report server. However, i have an
existing
> application with forms authentication in which I want to embed url access
to
> the report server. I was hoping that there would be code samples or an
> article for this particular issue. I don't need to present another form to
> the user to capture credentials. Can i use my existing forms
authentication
> ticket or do I need to create a new one. Do I call the LogonUser
webservice
> to create a cookie for a user that has been created on the report manager.
It
> seems like there are a lot of people trying to solve the same problem, but
> not too many examples.
> "Teo Lachev" wrote:
> > Start here
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> > news:3A2F7D63-C267-4CED-A5CC-4B42186B98B6@.microsoft.com...
> > > Is there any sample code for writing a custom security extension? I
have
> > > already validated the user and I want to provide url access to the
report
> > > server.
> > >
> > > "Teo" wrote:
> > >
> > > > Craig,
> > > >
> > > > You are right. You get prompted because the drilldown and
drillthough
> > > > interactive features require URL acccess and request goes out on the
> > client
> > > > side of the application.
> > > >
> > > > In a nutshell, if your reports have interactive features you need to
go
> > for
> > > > URL access. For Internet-oriented apps this means writing a custom
> > security
> > > > extension. It is not that involved to write and I have deployed an
> > > > application that uses a custom security extension in a production
> > > > environment. There are some gotchas to avoid but in general my
> > experience
> > > > writing custom security extensions have been positive and you will
learn
> > a
> > > > lot about how RS handles authentication and authorization.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ---
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > http://www.prologika.com
> > > >
> > > >
> > > > "Gash" <Gash@.discussions.microsoft.com> wrote in message
> > > > news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > > > > Just a thought: Have you tried to setup a individual Application
pool
> > that
> > > > works with your RSUser Account?
> > > > >
> > > > > "Craig HB" wrote:
> > > > >
> > > > > > I am building an asp.net app that will use reporting services to
> > show
> > > > reports within the application. Users login to the application and
when
> > they
> > > > need to see a report I use web services to render the report. The
> > asp.net
> > > > app and reporting services are on the same windows 2003 server (not
> > using
> > > > active directory).
> > > > > >
> > > > > > Because reporting services uses Windows authentication and does
not
> > > > allow anonymous access, I have created a windows account (called
> > "RSUser")
> > > > that has access to my reports. When the user runs a report, I pass
in
> > the
> > > > credentials for this windows account like this...
> > > > > >
> > > > > > rs.Credentials = New System.Net.NetworkCredential("RSUser",
> > "password",
> > > > "domain")
> > > > > >
> > > > > > This all works, and the report renders using the permissions
from
> > > > RSUser. The problem is that all the reports use treeviews for
drill-down
> > > > (and some use drill-through). When you expand a drill down you are
> > prompted
> > > > for a windows login. I think this is because this postback is now
coming
> > > > from the client PC, instead if from the asp.net app (i.e. on the
> > server),
> > > > and so reporting services needs to anthenticate this new user.
> > > > > >
> > > > > > The only solution that I have found for this is developing a
> > security
> > > > extension for reporting services...
> > > > > >
> > > > > >
> > > >
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > > > > >
> > > > > > ... but this seems like overkill and a very complicated process,
and
> > > > Microsoft says in the article that this is not fully tested and
should
> > not
> > > > be used in a production environment (but that where I need it for).
> > > > > >
> > > > > > Does anyone have a solution ?
> > > > > >
> > > > > > Craig HB
> > > >
> > > >
> > > >
> >
> >
> >|||Is there any sample code for writing a custom security extension? I have
already validated the user and I want to provide url access to the report
server.
"Teo" wrote:
> Craig,
> You are right. You get prompted because the drilldown and drillthough
> interactive features require URL acccess and request goes out on the client
> side of the application.
> In a nutshell, if your reports have interactive features you need to go for
> URL access. For Internet-oriented apps this means writing a custom security
> extension. It is not that involved to write and I have deployed an
> application that uses a custom security extension in a production
> environment. There are some gotchas to avoid but in general my experience
> writing custom security extensions have been positive and you will learn a
> lot about how RS handles authentication and authorization.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Gash" <Gash@.discussions.microsoft.com> wrote in message
> news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > Just a thought: Have you tried to setup a individual Application pool that
> works with your RSUser Account?
> >
> > "Craig HB" wrote:
> >
> > > I am building an asp.net app that will use reporting services to show
> reports within the application. Users login to the application and when they
> need to see a report I use web services to render the report. The asp.net
> app and reporting services are on the same windows 2003 server (not using
> active directory).
> > >
> > > Because reporting services uses Windows authentication and does not
> allow anonymous access, I have created a windows account (called "RSUser")
> that has access to my reports. When the user runs a report, I pass in the
> credentials for this windows account like this...
> > >
> > > rs.Credentials = New System.Net.NetworkCredential("RSUser", "password",
> "domain")
> > >
> > > This all works, and the report renders using the permissions from
> RSUser. The problem is that all the reports use treeviews for drill-down
> (and some use drill-through). When you expand a drill down you are prompted
> for a windows login. I think this is because this postback is now coming
> from the client PC, instead if from the asp.net app (i.e. on the server),
> and so reporting services needs to anthenticate this new user.
> > >
> > > The only solution that I have found for this is developing a security
> extension for reporting services...
> > >
> > >
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > >
> > > ... but this seems like overkill and a very complicated process, and
> Microsoft says in the article that this is not fully tested and should not
> be used in a production environment (but that where I need it for).
> > >
> > > Does anyone have a solution ?
> > >
> > > Craig HB
>
>|||Teo. Is it possible to use web forms authentication with the standard
edition of RS?
If not, I'm guessing there is no other way to use the viewer over the
Internet..
Thanks, AHH
BTW: I bought your book - best one out there..|||Thanks. No, extending RS requires Enterprise Edition. Sorry.
How about generating reports on the server side of the app and sacrificing
the interactive features and the toolbar?
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"AHH" <AHH@.discussions.microsoft.com> wrote in message
news:13CACEA1-BD84-4D6A-BB25-63D43E0F56A8@.microsoft.com...
> Teo. Is it possible to use web forms authentication with the standard
> edition of RS?
> If not, I'm guessing there is no other way to use the viewer over the
> Internet..
> Thanks, AHH
> BTW: I bought your book - best one out there..

authentication Mode

I am developing a client/server application using sql server as the back end
.
This app must allow for two login scenarios other than the usual one
user/one workstation. First, the app must allow users to log in from any
machine on the network even when using a machine running under a different
users login. Second, the app must be available to someone that does not hav
e
a windows login, eg a field worker that needs to access the app occasioniall
y
using a machine running under a different users login. For these reasons, I
have been using mixed mode authentication which does the job. Since all the
documentation seems to recommend Windows authentication mode and mixed mode
is for backward compability, am I missing something here? Can I handle the
two scenarios, particularly the second using Windows Authentication?
JBConsider using an Application Role rather than user level security.
Look in Books Online for "Establishing Application Security and Application
Roles".
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JB" <JB@.discussions.microsoft.com> wrote in message
news:AA8A22DC-D007-4CBE-BFF8-758693B9FCC0@.microsoft.com...
>I am developing a client/server application using sql server as the back
>end.
> This app must allow for two login scenarios other than the usual one
> user/one workstation. First, the app must allow users to log in from any
> machine on the network even when using a machine running under a different
> users login. Second, the app must be available to someone that does not
> have
> a windows login, eg a field worker that needs to access the app
> occasionially
> using a machine running under a different users login. For these reasons,
> I
> have been using mixed mode authentication which does the job. Since all
> the
> documentation seems to recommend Windows authentication mode and mixed
> mode
> is for backward compability, am I missing something here? Can I handle
> the
> two scenarios, particularly the second using Windows Authentication?
> JB|||That's hard to say as this line doesn't make sense:
"Second, the app must be available to someone that does not
have a windows login, eg a field worker that needs to
access the app occasionially using a machine running under
a different users login"
So what login is the "different users login" - but the user
doesn't have a login?
It really depends on what login is being used, if the field
work is accessing a machine in a domain, if it's multiple
domains, depends on trusts that may or may not be setup,
etc.
In terms of the other issue, Machines and logins are two
different things. If I have my windows login setup for
access to a SQL Server box in my domain, it doesn't matter
what machine I use. If I login into the network, that's the
credentials that are used no matter what machine I may be
logged into.
If users are logging into the domain with all different
logins, accessing network resources with various logins then
you have a security mess at the network level which will
lead to security messes in SQL Server as well when
implementing Windows authentication.
-Sue
On Fri, 22 Sep 2006 10:17:01 -0700, JB
<JB@.discussions.microsoft.com> wrote:

>I am developing a client/server application using sql server as the back en
d.
> This app must allow for two login scenarios other than the usual one
>user/one workstation. First, the app must allow users to log in from any
>machine on the network even when using a machine running under a different
>users login. Second, the app must be available to someone that does not ha
ve
>a windows login, eg a field worker that needs to access the app occasionial
ly
>using a machine running under a different users login. For these reasons,
I
>have been using mixed mode authentication which does the job. Since all th
e
>documentation seems to recommend Windows authentication mode and mixed mode
>is for backward compability, am I missing something here? Can I handle the
>two scenarios, particularly the second using Windows Authentication?
>JB

Tuesday, March 20, 2012

Authentication failure - can't find domain accounts

We're getting an error where we can't add a login with the full dns name of a user - domain.xyz\user, for example. Get an error 15401, "Windows NT user or group domain.xyz\user' not found". The domain has a different Netbios name and DNS domain names, so we can add the user when we use the form "netbiosname\user". So far so good.

Unfortunately, we have another application - Office Share Point Server whose shared services provider won't run, giving errors in the event log every 60 seconds that "Windows NT user or group 'domain.xyz\user' not found".

It looks as if SQL insists upon listing users in the form netbiosdomainname\user, and applications that look for domain.xyz\user simply fail to authenticate.

Suggestions?

jnfranc at yahoo period com

SQL Server only understands NetBIOS names for Windows principals (i.e. when creating a login), but authentication via SSPI should work normally.

Please let us know if you still require help on this issue, if so, we will need more details regarding the failure, if possible the SQL Server error number/message.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Authentication failure - can't find domain accounts

It looks as if SQL insists upon listing users in the form netbiosdomainname\user, and applications that look for domain.xyz\user simply fail to authenticate.

Suggestions?

jnfranc at yahoo period com

SQL Server only understands NetBIOS names for Windows principals (i.e. when creating a login), but authentication via SSPI should work normally.

Please let us know if you still require help on this issue, if so, we will need more details regarding the failure, if possible the SQL Server error number/message.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

sql

authentication error

I've been having a problem connecting to my sql 2000 server database.
The error reads...
Server Error in '/ASP' Application.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for
user 'NT AUTHORITY\NETWORK SERVICE'.
Source Error:
Line 30: //{
Line 31: Response.Write("before the open");
Line 32: objConn.Open();
Line 33: //SQLConnection.Open();
Line 34: //SqlCommand objCmd = new SqlCommand(sql,
objConn);
My web.config file reads:
<configuration>
<appSettings>
<add key="Andrew" value="Data Source=localhost;Initial
Catalog=Andrew;User Id=xxxx;Password=xxxxxxxxx"/>
</appSettings>
<system.web>
<compilation defaultLanguage="C#" debug="true" />
<customErrors mode="Off" />
<globalization requestEncoding="UTF-8" responseEncoding="UTF-8" />
</system.web>
</configuration>
My connection string in my C# file reads...
protected SqlConnection objConn = new
SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings["Andrew"]);
What I've done so far:
Under security in SQL server I've set up an account with the ID. It
shows type as Standard, Server access as Permit and Default Database as
Andrew.
Under my SQL Server Login Properties I've set the Authentication to
"SQL Server Authentication" and entered the password.
Under the defaults menu I've set the database to Andrew.
Under server roles "System Admins" is checked.
Under the Database Access tab my user ID is assigned to the
"Andrew" database with a checkmark in the permit box.
Database roles for Andrew are set to public and db_owner.
I can't seem to find my way around this error. Any help would be
greatly appreciated!
Thanks,
Andrew
Andrew,
When using ASP.NET the default security contect is the service accoutn that
IIS is running under. This is called using a trusted conneciton. By default
this account is not given access to SQL Server; to resolve this you should
give the correct permissions to SQL Server so the account that IIS is
running under has permissions to access SQL. Your connection is currently
connecting in this mode.
Adding "Integrated Security=false" to your connection string will turn this
feature off (then you should be good to go).
If you are inclined to use Integrated Authentication you should
1. Add Integrated Security=SSPI (and remove the user and password from your
conneciton string)
2. <system.web>
<authentication mode = "windows" />
<identity impersonate="true" />
</system.web>
3. Then open the IIS Admin tool and go to the properties window of where
your application is running. You then choose the Directory Security tab and
uncheck Anonymous & provide the username and password for the user. This
changes the context of the applicaiton to run under your user.
4. Grant the appropriate privilages to the user.
Brad Sarsfield [MSFT] bradsa(at)microsoft.com
This posting is provided "AS IS", with no warranties, and confers no rights.
<andrew.grande@.gmail.com> wrote in message
news:1127878854.885819.309560@.g49g2000cwa.googlegr oups.com...
> I've been having a problem connecting to my sql 2000 server database.
> The error reads...
> Server Error in '/ASP' Application.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.Data.SqlClient.SqlException: Login failed for
> user 'NT AUTHORITY\NETWORK SERVICE'.
> Source Error:
> Line 30: //{
> Line 31: Response.Write("before the open");
> Line 32: objConn.Open();
> Line 33: //SQLConnection.Open();
> Line 34: //SqlCommand objCmd = new SqlCommand(sql,
> objConn);
>
> My web.config file reads:
> <configuration>
> <appSettings>
> <add key="Andrew" value="Data Source=localhost;Initial
> Catalog=Andrew;User Id=xxxx;Password=xxxxxxxxx"/>
> </appSettings>
> <system.web>
> <compilation defaultLanguage="C#" debug="true" />
> <customErrors mode="Off" />
> <globalization requestEncoding="UTF-8" responseEncoding="UTF-8" />
> </system.web>
> </configuration>
> My connection string in my C# file reads...
> protected SqlConnection objConn = new
> SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings["Andrew"]);
> What I've done so far:
> Under security in SQL server I've set up an account with the ID. It
> shows type as Standard, Server access as Permit and Default Database as
> Andrew.
> Under my SQL Server Login Properties I've set the Authentication to
> "SQL Server Authentication" and entered the password.
> Under the defaults menu I've set the database to Andrew.
> Under server roles "System Admins" is checked.
> Under the Database Access tab my user ID is assigned to the
> "Andrew" database with a checkmark in the permit box.
> Database roles for Andrew are set to public and db_owner.
> I can't seem to find my way around this error. Any help would be
> greatly appreciated!
> Thanks,
> Andrew
>

authentication error

I've been having a problem connecting to my sql 2000 server database.
The error reads...
Server Error in '/ASP' Application.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for
user 'NT AUTHORITY\NETWORK SERVICE'.
Source Error:
Line 30: //{
Line 31: Response.Write("before the open");
Line 32: objConn.Open();
Line 33: //SQLConnection.Open();
Line 34: //SqlCommand objCmd = new SqlCommand(sql,
objConn);
My web.config file reads:
<configuration>
<appSettings>
<add key="Andrew" value="Data Source=localhost;Initial
Catalog=Andrew;User Id=xxxx;Password=xxxxxxxxx"/>
</appSettings>
<system.web>
<compilation defaultLanguage="C#" debug="true" />
<customErrors mode="Off" />
<globalization requestEncoding="UTF-8" responseEncoding="UTF-8" />
</system.web>
</configuration>
My connection string in my C# file reads...
protected SqlConnection objConn = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["An
drew"]);
What I've done so far:
Under security in SQL server I've set up an account with the ID. It
shows type as Standard, Server access as Permit and Default Database as
Andrew.
Under my SQL Server Login Properties I've set the Authentication to
"SQL Server Authentication" and entered the password.
Under the defaults menu I've set the database to Andrew.
Under server roles "System Admins" is checked.
Under the Database Access tab my user ID is assigned to the
"Andrew" database with a checkmark in the permit box.
Database roles for Andrew are set to public and db_owner.
I can't seem to find my way around this error. Any help would be
greatly appreciated!
Thanks,
AndrewAndrew,
When using ASP.NET the default security contect is the service accoutn that
IIS is running under. This is called using a trusted conneciton. By default
this account is not given access to SQL Server; to resolve this you should
give the correct permissions to SQL Server so the account that IIS is
running under has permissions to access SQL. Your connection is currently
connecting in this mode.
Adding "Integrated Security=false" to your connection string will turn this
feature off (then you should be good to go).
If you are inclined to use Integrated Authentication you should
1. Add Integrated Security=SSPI (and remove the user and password from your
conneciton string)
2. <system.web>
<authentication mode = "windows" />
<identity impersonate="true" />
</system.web>
3. Then open the IIS Admin tool and go to the properties window of where
your application is running. You then choose the Directory Security tab and
uncheck Anonymous & provide the username and password for the user. This
changes the context of the applicaiton to run under your user.
4. Grant the appropriate privilages to the user.
Brad Sarsfield [MSFT] bradsa(at)microsoft.com
This posting is provided "AS IS", with no warranties, and confers no rights.
<andrew.grande@.gmail.com> wrote in message
news:1127878854.885819.309560@.g49g2000cwa.googlegroups.com...
> I've been having a problem connecting to my sql 2000 server database.
> The error reads...
> Server Error in '/ASP' Application.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.Data.SqlClient.SqlException: Login failed for
> user 'NT AUTHORITY\NETWORK SERVICE'.
> Source Error:
> Line 30: //{
> Line 31: Response.Write("before the open");
> Line 32: objConn.Open();
> Line 33: //SQLConnection.Open();
> Line 34: //SqlCommand objCmd = new SqlCommand(sql,
> objConn);
>
> My web.config file reads:
> <configuration>
> <appSettings>
> <add key="Andrew" value="Data Source=localhost;Initial
> Catalog=Andrew;User Id=xxxx;Password=xxxxxxxxx"/>
> </appSettings>
> <system.web>
> <compilation defaultLanguage="C#" debug="true" />
> <customErrors mode="Off" />
> <globalization requestEncoding="UTF-8" responseEncoding="UTF-8" />
> </system.web>
> </configuration>
> My connection string in my C# file reads...
> protected SqlConnection objConn = new
> SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["
Andrew"]);
> What I've done so far:
> Under security in SQL server I've set up an account with the ID. It
> shows type as Standard, Server access as Permit and Default Database as
> Andrew.
> Under my SQL Server Login Properties I've set the Authentication to
> "SQL Server Authentication" and entered the password.
> Under the defaults menu I've set the database to Andrew.
> Under server roles "System Admins" is checked.
> Under the Database Access tab my user ID is assigned to the
> "Andrew" database with a checkmark in the permit box.
> Database roles for Andrew are set to public and db_owner.
> I can't seem to find my way around this error. Any help would be
> greatly appreciated!
> Thanks,
> Andrew
>

Authentication

it's still 'windows authentication only'. your sql login is created/valid
but it will not allow you to connect to sqlserver.
-oj
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...
>I just wonder my office server setup:
> In EM:
> 1) Right click the instance, select 'Edit SQL Server Registration
> properties...'
> 2) The 'Use Windows authentication' radio button is selected
> 3) Right click to select the instance, select 'Properties'
> 4) Security tab
> 5) 'SQL Server and Windows' authentication radio button is selected
> 6) Then I created a SQL Server login, eg. username is 'Alan', password is
> also 'Alan'
> So is this SQL Server 'Windows only' or 'SQL Server and Windows'
> authentication ?
>
>Its mixed mode authentication enabled.
You have registered server using windows authentication but you can
connect also using sql server login.
Regards
Amish Shah
oj wrote:
[vbcol=seagreen]
> it's still 'windows authentication only'. your sql login is created/valid
> but it will not allow you to connect to sqlserver.
>
> --
> -oj
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...|||I just wonder my office server setup:
In EM:
1) Right click the instance, select 'Edit SQL Server Registration
properties...'
2) The 'Use Windows authentication' radio button is selected
3) Right click to select the instance, select 'Properties'
4) Security tab
5) 'SQL Server and Windows' authentication radio button is selected
6) Then I created a SQL Server login, eg. username is 'Alan', password is
also 'Alan'
So is this SQL Server 'Windows only' or 'SQL Server and Windows'
authentication ?|||it's still 'windows authentication only'. your sql login is created/valid
but it will not allow you to connect to sqlserver.
-oj
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...
>I just wonder my office server setup:
> In EM:
> 1) Right click the instance, select 'Edit SQL Server Registration
> properties...'
> 2) The 'Use Windows authentication' radio button is selected
> 3) Right click to select the instance, select 'Properties'
> 4) Security tab
> 5) 'SQL Server and Windows' authentication radio button is selected
> 6) Then I created a SQL Server login, eg. username is 'Alan', password is
> also 'Alan'
> So is this SQL Server 'Windows only' or 'SQL Server and Windows'
> authentication ?
>
>|||Its mixed mode authentication enabled.
You have registered server using windows authentication but you can
connect also using sql server login.
Regards
Amish Shah
oj wrote:
[vbcol=seagreen]
> it's still 'windows authentication only'. your sql login is created/valid
> but it will not allow you to connect to sqlserver.
>
> --
> -oj
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...|||Alan wrote:
> I just wonder my office server setup:
> In EM:
> 1) Right click the instance, select 'Edit SQL Server Registration
> properties...'
> 2) The 'Use Windows authentication' radio button is selected
> 3) Right click to select the instance, select 'Properties'
> 4) Security tab
> 5) 'SQL Server and Windows' authentication radio button is selected
> 6) Then I created a SQL Server login, eg. username is 'Alan', password is
> also 'Alan'
> So is this SQL Server 'Windows only' or 'SQL Server and Windows'
> authentication ?
>
>
The authentication mode specified in the Registration properties has
nothing to do with the authentication mode that the server is using, it
only tells Enterprise Manager what sort of authentication method to use
when connecting to the server.
In your case, your SERVER is setup for mixed-mode authentication,
meaning it will accept either Windows credentials, or a valid SQL
login/password combo.
When you connect to it using your current Enterprise Manager
registration, EM is passing your current Windows login credentials to
the server, your SQL login "Alan" isn't being used at all. You can
confirm this by looking at the sysprocesses table after connecting from
EM, you'll see your spid, along with the login name that you're
connected with.|||my bad (should have read your post carefully before answering)...>> 5) 'SQL
Server and Windows' authentication radio button is selected<<
means the server is setup for mixed mode. Under this mode, you can connect
to sqlserver with either windows acct or sql login.
If you have properly created "alan" sql login, you should be able to connect
to sqlserver with that acct. The easiest way to test is to open Query
Analyzer (from Tools menu) and use "alan" as the credential.
-oj
"oj" <nospam_ojngo@.home.com> wrote in message
news:u$4pxhRkGHA.1204@.TK2MSFTNGP02.phx.gbl...
> it's still 'windows authentication only'. your sql login is created/valid
> but it will not allow you to connect to sqlserver.
>
> --
> -oj
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...
>|||Alan wrote:
> I just wonder my office server setup:
> In EM:
> 1) Right click the instance, select 'Edit SQL Server Registration
> properties...'
> 2) The 'Use Windows authentication' radio button is selected
> 3) Right click to select the instance, select 'Properties'
> 4) Security tab
> 5) 'SQL Server and Windows' authentication radio button is selected
> 6) Then I created a SQL Server login, eg. username is 'Alan', password is
> also 'Alan'
> So is this SQL Server 'Windows only' or 'SQL Server and Windows'
> authentication ?
>
>
The authentication mode specified in the Registration properties has
nothing to do with the authentication mode that the server is using, it
only tells Enterprise Manager what sort of authentication method to use
when connecting to the server.
In your case, your SERVER is setup for mixed-mode authentication,
meaning it will accept either Windows credentials, or a valid SQL
login/password combo.
When you connect to it using your current Enterprise Manager
registration, EM is passing your current Windows login credentials to
the server, your SQL login "Alan" isn't being used at all. You can
confirm this by looking at the sysprocesses table after connecting from
EM, you'll see your spid, along with the login name that you're
connected with.|||my bad (should have read your post carefully before answering)...>> 5) 'SQL
Server and Windows' authentication radio button is selected<<
means the server is setup for mixed mode. Under this mode, you can connect
to sqlserver with either windows acct or sql login.
If you have properly created "alan" sql login, you should be able to connect
to sqlserver with that acct. The easiest way to test is to open Query
Analyzer (from Tools menu) and use "alan" as the credential.
-oj
"oj" <nospam_ojngo@.home.com> wrote in message
news:u$4pxhRkGHA.1204@.TK2MSFTNGP02.phx.gbl...
> it's still 'windows authentication only'. your sql login is created/valid
> but it will not allow you to connect to sqlserver.
>
> --
> -oj
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...
>|||So in 'registration properties' determines what mode is using to connect
when opening the 'EM' to connection SQL Server ?
Only the 'Properties' determine how the login connect the server ?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23tqJ3YUkGHA.5036@.TK2MSFTNGP04.phx.gbl...
> Alan wrote:
> The authentication mode specified in the Registration properties has
> nothing to do with the authentication mode that the server is using, it
> only tells Enterprise Manager what sort of authentication method to use
> when connecting to the server.
> In your case, your SERVER is setup for mixed-mode authentication, meaning
> it will accept either Windows credentials, or a valid SQL login/password
> combo.
> When you connect to it using your current Enterprise Manager registration,
> EM is passing your current Windows login credentials to the server, your
> SQL login "Alan" isn't being used at all. You can confirm this by looking
> at the sysprocesses table after connecting from EM, you'll see your spid,
> along with the login name that you're connected with.

Authentication

Hi
i want that a user cannot login to EM with windows authentication..
ThankxxxMuhammad Bilal (MuhammadBilal@.discussions.microsoft.com) writes:
> i want that a user cannot login to EM with windows authentication..
Then don't give that user access to SQL Server. There is no way to lock
out a user from accessing SQL Server from a certain application. Thus,
you should never grant a user rights to do things you don't want him to do.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Authentication

Hi! I have a problem enabling access to my 'sa' login to a couple of
application db's it fails each time. I think the problem lies herein:I am on
Sql 2005 BTW...When I go to server properties/security it shows,
appropriately, Mixed mode authentication. However, when I click on "View
Connection Properties" in the connection section of the server properties
page it shows windows authentiication. Therefore, I believe that when I try
to add the db's to the mapping for 'sa' the server is looking for a windows
account which of course does not exist. Any advice as to how I might proceed?
Thanks all...I do not want to create a windows account for sa...Adios...
Well, 'view connection properties' refers to your current conn props... so
that just means that you're connecting to the server with windows auth... it
has nothing to do with the server security setting.
Since you didn't really provide an error message I'll say one thing to check
would be whether sa owns the DB in question. You can't add an acct as a user
if that acct already owns the DB.
"Walt Herman" wrote:

> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I try
> to add the db's to the mapping for 'sa' the server is looking for a windows
> account which of course does not exist. Any advice as to how I might proceed?
> Thanks all...I do not want to create a windows account for sa...Adios...
|||If you install SQL Server in Windows Authentication Mode, then change to
Mixed Authentication Mode, the sa account is disabled.
Use ALTER LOGIN to enable the account. For more information, see the topic
How to: Change Server Authentication Mode in Books Online.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Walt Herman" <WaltHerman@.discussions.microsoft.com> wrote in message
news:4C4AED35-F285-477F-BE3C-8961098826E8@.microsoft.com...
> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am
> on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I
> try
> to add the db's to the mapping for 'sa' the server is looking for a
> windows
> account which of course does not exist. Any advice as to how I might
> proceed?
> Thanks all...I do not want to create a windows account for sa...Adios...

Authentication

Hi! I have a problem enabling access to my 'sa' login to a couple of
application db's it fails each time. I think the problem lies herein:I am on
Sql 2005 BTW...When I go to server properties/security it shows,
appropriately, Mixed mode authentication. However, when I click on "View
Connection Properties" in the connection section of the server properties
page it shows windows authentiication. Therefore, I believe that when I try
to add the db's to the mapping for 'sa' the server is looking for a windows
account which of course does not exist. Any advice as to how I might proceed
?
Thanks all...I do not want to create a windows account for sa...Adios...Well, 'view connection properties' refers to your current conn props... so
that just means that you're connecting to the server with windows auth... it
has nothing to do with the server security setting.
Since you didn't really provide an error message I'll say one thing to check
would be whether sa owns the DB in question. You can't add an acct as a use
r
if that acct already owns the DB.
"Walt Herman" wrote:

> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am
on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I tr
y
> to add the db's to the mapping for 'sa' the server is looking for a window
s
> account which of course does not exist. Any advice as to how I might proce
ed?
> Thanks all...I do not want to create a windows account for sa...Adios...|||If you install SQL Server in Windows Authentication Mode, then change to
Mixed Authentication Mode, the sa account is disabled.
Use ALTER LOGIN to enable the account. For more information, see the topic
How to: Change Server Authentication Mode in Books Online.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Walt Herman" <WaltHerman@.discussions.microsoft.com> wrote in message
news:4C4AED35-F285-477F-BE3C-8961098826E8@.microsoft.com...
> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am
> on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I
> try
> to add the db's to the mapping for 'sa' the server is looking for a
> windows
> account which of course does not exist. Any advice as to how I might
> proceed?
> Thanks all...I do not want to create a windows account for sa...Adios...

Monday, March 19, 2012

auditing the table

Hi,
I would like to audit table but I do not want to use
profiler. I need to know NT user name not just sql server
standard login for the user. The problem is that somebody
ocasssionally deletes the data and I would like to find
out who is doing it.
Any view is appreciated.You could create a TRIGGER on the table. eg :-
CREATE TRIGGER reminder
ON titles
FOR DELETE
AS
EXEC master..xp_sendmail 'ToYou',
'Someone just deleted a row'
GO
this could easily be enhanced to log to a table, you can capture the user
name of who did the deletion by capturing USER_NAME
HTH
Ryan Waight, MCDBA, MCSE
"Mirna" <mstojsic@.hotmail.com> wrote in message
news:2739901c38f3d$83e5d340$a601280a@.phx.gbl...
> Hi,
> I would like to audit table but I do not want to use
> profiler. I need to know NT user name not just sql server
> standard login for the user. The problem is that somebody
> ocasssionally deletes the data and I would like to find
> out who is doing it.
> Any view is appreciated.
>

Sunday, March 11, 2012

auditing logins

SQL 2k, Windows authentication only
I'd like to be able to monitor when a particular developer attempts to
login to sql server. (successful or not)
Ideally, I'd like an alert to fire when that user logs in or attempts
and fails to log in. How can I set up an alert for successful login?
Is the auditing information stored in tables? I could schedule a job to
select for that developer every hour or so.
I know the log files are written to disk, so should I just write a small
program to search through these?
I appreciate any/all suggestions
Tom
--
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.Login auditing is done in the Windows Security event log. There is an
option to set it for successful logins, failed logins or both.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:ur3wDW12GHA.3516@.TK2MSFTNGP06.phx.gbl...
> SQL 2k, Windows authentication only
> I'd like to be able to monitor when a particular developer attempts to
> login to sql server. (successful or not)
> Ideally, I'd like an alert to fire when that user logs in or attempts and
> fails to log in. How can I set up an alert for successful login?
> Is the auditing information stored in tables? I could schedule a job to
> select for that developer every hour or so.
> I know the log files are written to disk, so should I just write a small
> program to search through these?
> I appreciate any/all suggestions
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.

Sunday, March 25, 2012

authorization and permissions

authorization and permissions

authorization and permissions

authorization and permissions

authorization

author of package affects login?

Authentification problem with SQL 2k, IIS and Linked Server

Authentication problems

Thursday, March 22, 2012

Authentication over the internet

authentication Mode

Tuesday, March 20, 2012

Authentication failure - can't find domain accounts

Authentication failure - can't find domain accounts

authentication error

authentication error

Authentication

Authentication

Authentication

Authentication

Monday, March 19, 2012

auditing the table

Sunday, March 11, 2012

auditing logins

Attach remote DB file

Blog Archive

About Me