Showing posts with label basic. Show all posts
Showing posts with label basic. Show all posts

Tuesday, March 20, 2012

Authentication failed error

Receiving authentication failed error on a SQL driven website.
Basic directory structure

admin
|
|
|--> inc
|
|
|-->setdefaults.inc

inc
|
|
|-->setdefaults.inc and database.mdb

The setdefaults.inc in the admin/inc folder is pointing to the database.mdb in the main inc folder. When I try to access the admin side of the website, I'm getting an authentication failed error. The public side of the website functions properly (displays all files added to the database before the error starting occuring and the search function works properly).

Our ISP migrated their servers about a month ago. Have been unable to confirm with them if the IP or path to the SQL database changed. I don't think it has because the main site still functions properly and it's the same database.

Does anyone have any suggestions on how I can troubleshoot this problem further?

Thanks for your help!

DeborahAre you using the same user accounts to log onto the database for both the admin and customer parts of the site?|||Yes - exactly the same info. The ISP finally got back to me (two months later) and gave me a host name, IP address, database name, UID and PW. The original coding done by my web designer has the fields Server Name, database name, UID and PW.

Do I just replace the Server name with the IP address and do I need to put the host name anywhere. If so, where?

Deborah|||You should be able to replace the Server with the IP address without any problem. Do you know what type of access your account has for the database? The user account may be limited to read only access (or somthing similar) and as a result will not allow the more advanced sections of the admin (update, insert etc).

Originally posted by vascorp
Yes - exactly the same info. The ISP finally got back to me (two months later) and gave me a host name, IP address, database name, UID and PW. The original coding done by my web designer has the fields Server Name, database name, UID and PW.

Do I just replace the Server name with the IP address and do I need to put the host name anywhere. If so, where?

Deborah|||I went line by line through my developers code, it looks like the authentication info on the public side is REMmed out so that solves the mystery as to why it appeared to be authenticating properly on one side and not on the other.

I tried replacing the server name with the IP address. It doesn't work. I just found out that the ISP provider now uses MySQL. Is that entirely different from MS SQL Server?

Thanks

Deborah|||It is a different, and as a result I imagine you will need a different connection string.

You will need somthing like

oConn.Open "Driver={mySQL};" & _
"Server=ipAddress;" & _
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database=mydb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

Authentication across internet

From what I have understood, Reporting Services could be configured to use Basic Authentication. In this way, a user can access a RS-server across the internet and he/she will be prompted for a valid username/password when trying to access.

However, from what I can understand this username and password will be sent uncrypted over the Internet (from the client machine to the RS-server), right?

My problem is, I need to give access to users acress the internet to a RS-server. The users uses all kind of operating systems (Windows, Linux, MacOS tec). And the communication needs to be encrypted.

How would you recommend me to implement security for this solution? I guess some kind of "Forms authentication" will have to be used? But how to make sure the data traffic is encrypted? And where do you recommend me to store the username and passwords? In an Active Directory on the server side, or in a separate database on the server side?

regards Andreas

You need to set up your report server to use SSL, that way all communication is encrypted. This would be done in IIS but also requires some config chages to reporting services.

Both Basic authentication and Forms would work in this scenario.

Use AD for the user accounts if possible.

|||

Thank you for your quick reply!
My SSL experience is very limited as well, but I guess it means that I need to buy a certificate from some trusted store? Otherwise the clients will always be warned when trying to connect, right?

Regards Andreas

|||If you have a fix client list, give them a link to download your own CA public key should be good enough. Of coz if its facing internet I suggest you pay for SSL. Not only your authentication, your report data are flying nude via the line, I guess that might be another issue and reason why SSL is required.|||

Ok, thank you for your answers! An SSL-certificate it will be!

Just so that I didn't missunderstood. If I configure IIS to use SSL, the login name and password will be safly encrypted, even if I use Basic authentication, or?

Regards Andreas

|||Yes, ALL communication is encrypted.sql

Thursday, March 8, 2012

Audit trail - Sql Server 2000

Hi,
Greetings to all.
We are building up a new application using SQL Server 2000 & .NET.
Need to build basic “Audit trail” process, in which need to store {User_
Id/
DateTime/ Status/Process Name}.
What’s the best strategy for implementing “Audit trail” in SQL Server
2000
Applications?
Thanks for your time.I'm assuming that "Status" and "Process Name" are not columns in tables, but
rather logical names in your application. The simplest way is to use
something like .NET Enterprise Library or even build your own simple logic
in your application to keep trace of this.
Typically, in the database, if you want to track changes made to your
tables, you use triggers, but in your case, I think you want to track what
"functions" a user is using?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
> Hi,
> Greetings to all.
> We are building up a new application using SQL Server 2000 & .NET.
> Need to build basic "Audit trail" process, in which need to store
> {User_Id/
> DateTime/ Status/Process Name}.
> What's the best strategy for implementing "Audit trail" in SQL Server 2000
> Applications?
> Thanks for your time.
>|||Thanks for your response. Let me explain my situation once again:-
e.g. I have a screen & different status of order on that screen i.e.
Created/Billed/Processed/Manufactured/Shipped.
What I want to do is, I want to record, which user had changed above order
status at what time?
Which means for each record, I want to build an Audit Trail?
I am aware that ,I can create my own Audit table & can create DB Triggers
for recording details ,or stored Proc can do same for me…
I am looking for what’s the best strategy to build an Audit trail.
Thank you very much for your precious time & Valuable inputs.
Regards.
"SriSamp" wrote:

> I'm assuming that "Status" and "Process Name" are not columns in tables, b
ut
> rather logical names in your application. The simplest way is to use
> something like .NET Enterprise Library or even build your own simple logic
> in your application to keep trace of this.
> Typically, in the database, if you want to track changes made to your
> tables, you use triggers, but in your case, I think you want to track what
> "functions" a user is using?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
>
>|||I would suggest triggers and audit tables. Try to keep the trigger as light
as possible. What kind of load (# of users, volume of data, etc.) does your
system have?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
> Thanks for your response. Let me explain my situation once again:-
> e.g. I have a screen & different status of order on that screen i.e.
> Created/Billed/Processed/Manufactured/Shipped.
> What I want to do is, I want to record, which user had changed above order
> status at what time?
> Which means for each record, I want to build an Audit Trail?
> I am aware that ,I can create my own Audit table & can create DB Triggers
> for recording details ,or stored Proc can do same for me.
> I am looking for what's the best strategy to build an Audit trail.
> Thank you very much for your precious time & Valuable inputs.
> Regards.
>
> "SriSamp" wrote:
>|||Hi,
Apart from Trigger & Audit table ,does sql server comes with default
keywords like AUdit ? Or any other Standard/better approach to hadle it ?
Oracle allows us to audit data using the ‘AUDIT’ command. For example,
‘AUDIT DELETE ON my_table;’
Regards
"Alain Quesnel" wrote:

> I would suggest triggers and audit tables. Try to keep the trigger as ligh
t
> as possible. What kind of load (# of users, volume of data, etc.) does you
r
> system have?
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
>
>|||Hi,
In terms of Users/Load ,it's 50 -75 users using it at one point.
"Alain Quesnel" wrote:

> I would suggest triggers and audit tables. Try to keep the trigger as ligh
t
> as possible. What kind of load (# of users, volume of data, etc.) does you
r
> system have?
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
>
>|||I don't think there are any default tools, but I'd personally steer
clear of triggers. While they're great at doing what they do, and lend
themselves naturally to an audit trail, I've found the main drawback of
them is that you can't see them. When someone else comes to maintain
your application, they would have to know that the triggers are there.
I know that with good documentation and skilled staff this would appear
simple, but you can't generally count on either.
My prefered method (given this drawback of triggers) is to write a
sufficiently generic auditing stored procedure, then within your insert
/ update / delete stored procs call this method. This has the advantage
that your auditing behaviour is centralised, and therefore if you need
to change it you won't need to iterate through the triggers, but also
that the operation is visible to anyone else who needs to pick up your
app.
I suspect this is a sufficiently controversial suggestion to get
utterly thrashed on here now, so... off you go guys, above the belt
with the punches please.|||Thanks for your reply.I agree to your suggestion to quite some extent.But i
am wondering , do we have an better way / inbuilt way of handling this ? Or
any more standard way (Best practise) of handling this ?
"Will" wrote:

> I don't think there are any default tools, but I'd personally steer
> clear of triggers. While they're great at doing what they do, and lend
> themselves naturally to an audit trail, I've found the main drawback of
> them is that you can't see them. When someone else comes to maintain
> your application, they would have to know that the triggers are there.
> I know that with good documentation and skilled staff this would appear
> simple, but you can't generally count on either.
> My prefered method (given this drawback of triggers) is to write a
> sufficiently generic auditing stored procedure, then within your insert
> / update / delete stored procs call this method. This has the advantage
> that your auditing behaviour is centralised, and therefore if you need
> to change it you won't need to iterate through the triggers, but also
> that the operation is visible to anyone else who needs to pick up your
> app.
> I suspect this is a sufficiently controversial suggestion to get
> utterly thrashed on here now, so... off you go guys, above the belt
> with the punches please.
>|||I don't know of any in-built methods, I'm pretty sure there aren't any.
However the information you'll be recording is usually fairly specific
to your app. My advice would be either to maintain auditing in the
stored procs, or, depending on the structure of your web app you could
enhance your data access layer to add a custom audit module that
controls all the auditing.
Personally I prefer keeping out of SQL server based auditing for
several reasons:
1) you can more easily re-use your auditing code
2) you can more easily audit to a centralised database for several apps
3) by auditing to a separate database you are not having to back up
tracing data along with your actual app data (though you do have to
consider re-synchronisation when restoring)
4) you are able to capture web app info more easily (e.g. if you're
using forms based authentication you can capture usernames, or you can
capture the page that the update originated from).
5) the auditing is then done for your application, rather than on your
db. This has the advantage that if someone else makes a custom update
to your database, you can't accuse the application of it. On the other
hand, you don't have the traceability of what was done (but personally
I prefer the idea that your app only audits things that it does).
6) the auditing is more visible and more maintainable
I could probably come up with more reasons, but this should indicate my
preference.
Perhaps someone else could recommend some off the shelf plug-ins for
this?
Will|||Just to give you a couple of more options to evaluate:
Have a server-side profiler trace going in which you capture the relevant ev
ents.
Use some of the 3:rd party (transaction) log reader tools, where some has ex
plicit audit
capabilities. The transaction log contains information about all modificatio
ns and some of these
tools can also log SELECT using a profiler trace in conjunction with the tra
nsaction log. I've
listed some of these log reader tools on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
> Hi,
> Greetings to all.
> We are building up a new application using SQL Server 2000 & .NET.
> Need to build basic "Audit trail" process, in which need to store {User_Id
/
> DateTime/ Status/Process Name}.
> What's the best strategy for implementing "Audit trail" in SQL Server 2000
> Applications?
> Thanks for your time.
>

Saturday, February 25, 2012

Attributes, Properties; Dimensions, Levels, and Hierarchies, oh my!

I have a most basic question: how to correctly access properties (attributes) of dimension members that are at a different level. Here's a simple query from AdventureWorks to illustrate:

Code Snippet

select

[Customer].[Customer].members

dimension properties

[customer].[customer].[postal code],

[customer].[postal code].[city]

on 1,

[Measures].[Internet Sales Amount]

on 0

from [Adventure Works]

This query works, but the city property is left out of the results (postal code is provided). How can I get City, State-Province, Country, etc, as properties in a query like this? (As the relationships are all correctly defined, and the levels for these attributes/properties are less restrictive than the query level, I'm totally missing why this doesn't work in either the form given -- but do I have the name perhaps wrong?)

Seperately, I have read (somewhere) that property access is to be discouraged in SSAS05, instead prefering direct attribute access via UDM; is there a better way to write these sorts of queries, say including City, etc in the output grid area?

Thanks as always!

To somewhat answer my own query, I have found that:

Code Snippet

with member [measures].[cc] as [customer].[city].MemberValue + ', ' + [customer].[state-province].MemberValue

select

[Customer].[Customer].members

dimension properties

[customer].[customer].[postal code],

[customer].[postal code].[city]

on 1,

{[measures].[cc], [Measures].[Internet Sales Amount]}

on 0

from [Adventure Works]

properly returns the customer "city, state" in the output grid area. But I am still interested in how to obtain the city and state-province instead as properties of the customer members on axis(1).....

Thanks again!