does anybody know why MS doesnt consider Basic Authentication a viable solution for Inter/Xtra net access to RS? I'm re reading the documentation at http://msdn2.microsoft.com/en-us/library/bb283249.aspx but so far am not getting what the Security Extension options accomplish that can't be accomplished with much less effort using some form of Basic Authentication.
View 25 RepliesOn my current project we have a requirement to re-authenticate the user when accessing sensitive information.
We want re-authenticate using standard NT logins against Active Directory (not a custom database or SSO.
This is trivial to configure using Basic Authentication, however I would like to use an ASP.NET login form. I would like to avoid writing a security extension as I do not want to perform custom authentication. I would like the web service to use it's built in authentication and authorisation mechanisms.
Is the above possible???
The following gives some more details about wht I've tried.
I have tried configuring the report server/manager with Forms authentication as in the sample but cannot get it to work with out implementing the security extensions.
I changed the web.config files and the policy files for permissioning my dll with FullTrust. I did not configure an extension as I want reporting services to use it's built in windows security mechanisms.
In the login page code behind I call the ReportingService2005.LogonUser() method which always throws the following exception:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: --
Reporting Services Error
The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) Get Online Help
SQL Server Reporting Services --.
I check the log file and it has the following:
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.logon_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
aspnet_wp!library!18!03/13/2007-11:38:23:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.FormatException: Input string was not in a correct format.
at System.Text.StringBuilder.FormatError()
at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
at System.String.Format(IFormatProvider provider, String format, Object[] args)
at Microsoft.Samples.ReportingServices.CustomSecurity.Logon.ServerBtnLogon_Click(Object sender, EventArgs e) in C:Program FilesMicrosoft SQL Server90SamplesReporting ServicesExtension SamplesFormsAuthentication SamplecsFormsAuthenticationLogon.aspx.cs:line 130
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
--- End of inner exception stack trace ---
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.logon_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
--- End of inner exception stack trace ---
aspnet_wp!library!18!03/13/2007-11:38:24:: i INFO: Exception dumped to: c:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles flags= ReferencedMemory, AllThreads, SendToWatson
aspnet_wp!library!1!03/13/2007-11:39:11:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., Could not load Authentication extension;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.
Am I missing something? Is this even possible? If not, then why isn't it possible?
It feels like I've been going round in circles on something that shouold be pretty trivial to configure.
Thanks in advance.
Adam.
Greetings. I have developed our own custom security extension, implemented forms based authentication, and can authenticate from report manager, report server and sql studio. So far so good.
However, when it comes to Authorization, i'm unclear in a few areas and would appreciate if someone could help me out with the following questions. It should be noted that in the code I have granted an administrator user full access to all operations and permissions, and then tested against both an administrator user and a normal user.
IAuthorizationExtension.GetPermissions summary says
"Returns the set of permissions granted a specific user for an item in the report server database."
Inparticular, the secDesc parameter is supposed to contain the security descriptor associated with the item.
However, with our extension this parameter is always null, even if I have already granted access for a user, which is confirmed through logging in CreateSecurityDescriptor.
Through the report manager or sql studio I can see that the permissions have been created, so I can't understand why I never see them in the GetPermissions method? This then (seems to) flow through to the various CheckAccess methods, where the users are authenticated, but are not authorized to perform any operations. i.e. in report manager a user has no folders or reports available.
Is RS authorization designed around the concept that the details will always be stored in it's own database?
Ideally, we'd like to have the various roles, users and function authorizations defined in our own security framework. This is working great for the authentication aspect of the extension, but unless there is a mechanism which exposes the details of the particular authorization process (e.g. the name of the folder being viewed or report being run), then I can't see a way we can implement it. Unless i'm missing something fundamental of course!
Using Report Builder as the Administrator user (or any other user), I can see no data models available, even though I have created them via Report Manager, and I get the following exception trying to open up the list of reports:
System.Web.Services.Protocols.SoapException: The permissions granted to user '' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation. at Microsoft.ReportingServices.Library.ListChildrenAction.PerformActionNow() at Microsoft.ReportingServices.Library.RSSoapAction.Execute() at Microsoft.ReportingServices.WebServer.ReportingService2005.ListChildren(String Item, Boolean Recursive, CatalogItem[]& CatalogItems)
I have implemented a report server proxy (inherited from ReportService2005) as per the example, to pass through the authorization cookie. Any clues as to what could be wrong?
Finally, I suspect part of my problem may be in assignment of users to System Roles ("System Administrator" and "System User"), I'm not sure if these are meant only for Windows Authentication? I can see no way of assigning these roles to any of my users using Forms Authentication.
Thanks for any help or advice you can give!
Hello there I have trying to figure out for days how to enable FullTrust for my Reporting Services security extension.
View 9 Replies View RelatedHi All,
My application is currently integration with reporting services using custom security extension. I have my own USER and ROLE database to determine who/which role can access certain reports.
My question is how to give access a report to be viewable by everyone? I dont want to assign all users or all roles to achieve this.
The other thing that I found out is, let say I change the name one of the user/role. Because of this, the authorization will fail because the old name/role is not in the DB anymore. Is this expected? or is there a workaround it?
Your help is appreciated.
Thanks!
Ok, I know it's a really basic question but I'm hoping someone will humor me. Not being a database expert I am wondering what the basic security differences are between a SQL server db and an Access db. What makes SQL a more secure choice for confidential information over Access.
I know from a useability SQL is the better choice being a multiuser access platform but I am not familar with security mechanisms employed.
I appreciate any info on this.
Thank you.
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
We're trying to determine the best way to implement an authentication mechanism for our reporting services solution. Basically, we are exposing a web application to the internet, and allowing a forms authenticated user to access our report server somewhere within our intranet to view reports. I've browsed msdn and found that there are two approaches:
1. create a custom authentication extension for reporting services
2. create a restricted domain user that is allowed to access the report server
Going with approach 2 and assuming I'm using a ReportViewer control, in order to authenticate with the report server (using the one domain user created specifically for report server access), are we supposed to just provide an instance of an object implementing IReportServerCredential containing the domain user information to the Credentials property of the ReportViewer.ServerReport before accessing the report? Is this the correct way to implement approach 2? Also, if implementing approach 2 is so simple compared to approach 1, why would anyone choose to implement custom authentication extension if they don't need fine-grain access control on the report server level?
Thanks for any help and suggestions!
Hey, I was just wondering if someone could point me in the right direction on an issue. I've got the authentication portion of a custom security extension working properly (while authorization is just sort of giving everyone a pass) and all of my Googling efforts seem to be bearing little fruit. Now that authentication is in place, whenever I try to deploy a report from Visual Studio, it comes up with an error.
The error is that it is getting the authentication html login page set in web.config (as I expected), while it was looking for an xml page. I'm a bit new to this side of things so I'm not really even sure where I should be looking to see what kind of xml page it wants and such.
I don't need anyone to bother explaining the entire thing to me as I should be able to figure it out once I can get a start, but as I said I just need a kick in the right direction because my Google-fu has failed
Hi,
I have installed the SSRS 2005 Forms based login security extension as per the sample and instructions that come with the product install. I have double checked my work and debugged up to the point explained here.
My user is in the database and I can get to the login page. I have traced the code and can see the user being authenticated properly by server.LogonUser, the authentication cookie being created and placed in the response object, and then the redirect url being set to the reports/pages/folder.aspx. I have employed tcptrace and can see the sqlAuthCookie set in subsequent posts.
Unfortunately the redirect to folder.aspx never happens. I keep returning to UILogon.aspx. I think their is something either wrong with the auth cookie or the web.config in the ReportServer IIS virtual directory. The relevant xml from the web.config is attached below.
I would appreciate any thoughts. I am running on W2K3 SP1. Thanks.
Mitch
<identity impersonate="false" />
<!--<authentication mode="Windows" />-->
<authentication mode="Forms">
<forms loginUrl="logon.aspx" name="sqlAuthCookie" timeout="60" path="/"></forms>
</authentication>
<authorization>
<deny users="?" />
</authorization>
Hi,
The custom security extension generates the following error, when try to access Report Manager. The specifications are as follows.
1. Its 64-bit SQL Server (Reporting Services) and installed as default instance.
2. The Report Server is accessible programmatically. I can publish, view reports without problems, but Report Manager gives an error.
3. Another instance (named instance) of Reporting Services with the same custom security extension on same server is working fine. Report Manager opens up without problems.
4. Both the instances working fine since 3 installation and configuration (3 months back). Now, only the Report Manager of the default instance is not openeing up.
ReportServerWebApp__08_22_2006_08_52_20.log
-------------------------------------------------------------------------
w3wp!ui!1!8/22/2006-08:52:56:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: HTTP status code --> 500
-------Details--------
Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper+CantCommunicateWithReportServerException: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
at Microsoft.ReportingServices.UI.Global.SecureAllAPI()
at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)
at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!1!8/22/2006-08:52:57:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:58:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
The service is running fine. Any suggestions/pointers would be greatly appreciated.
THanks in advance
Hello,
I have followed all the steps indicated in the Security Extensions Sample for RS 2005, and I have not changed any webpage code. However, I get the following errors:
- when accessing http://<computername>/ReportServer Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerlogon.aspx' is denied
- when accessing http://<computername>/Reports Error message 401.3: You do not have permission to view this directory or page using the credentials you supplied
Can someone help please?
Thank you!
I have a very confusing problem that I'm going to attempt to relay concisely:
I have a LAN on which I'm running an Intranet web site built in ASP.Net 2.0 and using SSRS / Sql Server 2005 on the same server.
The name of the box is "warehouse" or "warehouse.mydomain.us." When I ping "warehouse," I get a response from 137.86.166.8, as I should. Likewise, I get a response from 137.86.166.8 when I ping "warehouse.mydomain.us." If I do a "ping -a" on 137.86.166.8, it correctly resolves to "warehouse.mydomain.us." Based on the facts presented in this paragraph, I believe that my DNS is working as expected.
Using IE (6.0.2900.2180.xpsp_sp2_gdr.050301-1519) with "low" security settings, I navigate to http://warehouse.mydomain.us/lex/ (Lex is the name of the app) and it authenticates me to the index page (which calls the SSRS web service to get and display a list of available reports), except that I get the login prompt three times. If I hit F5 (refresh), I get three more login prompts and then I get a 401.1 error. From this point, no matter what I do, I can not see the index page again without closing and restarting IE.
I know that IE only passes credentials to certain sites, so I add my domain (http://warehouse.mydomain.us/) to the Local Intranet list ( Tools->Internet Options->Security->Local Intranet->Advanced) and then I can refresh and see my index page once, but I still get 3 login attempts and then the same thing happens -- I refresh and go through 3 logins and then get a 401.1.
If I now change my address bar to read http://137.86.166.8/lex, I get a single login and it works like a champ. This makes me think that there's a DNS problem, so I open up Mozilla Firefox and wouldn't you know it -- Firefox works like a champ all around -- one single sign-in, it keep credentials between Lex and SSRS, and it appears to use the DNS correctly. However, I can't use Firefox because I can't get any of the SSRS reports to look like they're supposed to in Mozilla-based browsers.
Argh.
I have set my IE securtiy settings to "Low." I have added the primary domain to the Local Intranet list and also to the Trusted Sites list (but you can't do both at the same time). I have even added the internal IP to my "hosts" file.
Does anyone know anything that might be a help to me in getting this figured out?
Other notes of interest:
My machine is an XP machine, but is not on the domain, but I do have a valid domain login that I use and it works in Firefox without problem.
I follow the instructions here to deploy the sample security extension: http://www.codeplex.com/MSFTRSProdSamples/Wiki/View.aspx?title=SS2005!Security%20Extension%20Sample&referringTitle=Home
After I have configure the config files and created the "UserAccounts" database I restart the IIS and tried to connect to Report Manager at http://localhost/Reports
This is the error i got: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
I went to Services and saw my Reporting Services is stopped. Tried to start Reporting Services and it timed out.
I am attempting to deploy the sample Security Extensions. When I navigate to <machinename>/reports in IE, it works fine and I am able to register the administrator. The user exists in the table correctly, but I am unable to login as the user. It keeps going back to the same uilogon.aspx redirect page.
It appears to be a similar error as http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=289636&SiteID=17
However, I am using the machine name the entire name, never referring to it as localhost. Any ideas? Thanks!
Hi,Just installed Norton Internet Security on an XP workstation that also hasSQL Server on it.I now find that I cannot access SQL Server and multiple messages are beingissued by NIS.When I switch off the Firewall & Intrusion Detection I can access SQLServer.Does anyone know how to configure NIS so that I can use it alongside SQLServer?Thanks,Mike.
View 1 Replies View Related
Couple of questions for the SQL Server Guru's out there.
SQLServer 2005
Web Hosting Provider
Ok I am developing a Web application in ASP.NET with AJAX, etc. etc. It will be some time before it is ready to roll out. As a mockup I created the same application in Microsoft Access and Visual Basic (VS 2008), which I can link the tables to the hosting provider on the internet. Works very well and speed is very acceptable. The want to start utilizing it with the mockup distributed app that I created.
My question is, is how secure is the data that is moving from the local application to SQLServer 2005 with the web hosting provider ? Is there anything that I can do to increase security ?
Thanks, any thoughts ?
Appreciate any suggestions or comments.
Roles Table:
RoleID, RoleName
<RoleID1>, Allow_ClientFolder
<RoleID2>, Allow_ClientReportA
<RoleID3>, Allow_ClientReportB
<RoleID4>, Allow_CompanyFolder
<RoleID5>, Allow_CompanyReportA
<RoleID6>, Allow_CompanyReportB
UsersInRoles Table:
UserID, RoleID
<UserID1>, <RoleID1>
<UserID1>, <RoleID2>
<UserID2>, <RoleID4>
<UserID2>, <RoleID5>
<UserID2>, <RoleID6>
<UserID2>, <RoleID1>
<UserID2>, <RoleID3>
e.g.
when the <UserID1> clicks on the "home" link show only "Client" folder and when <UserID1> clicks on the "Client" folder only "ClientReportA" will show.
Here is the accessable map for each user:
<UserID1>
Client
ClientReportA
<UserID2>
Client
ClientReportB
Company
CompanyReportA
CompanyReportB
Question:
What is the best way to implement CheckAccess? At first I thought I can access the item names and I can test authorization through that, but AceCollection contains operation permission of the accessing object.
Hi,
I am trying the get the custom security extension samples to work on my machine. I have installed everything according the sameple help file. However, i am getting a "Authentication Ticket is not issued by LogonUser." error after I attempted to log on to the report manager thru UILogon.aspx page. I already created an admin user. Upon a closer inspection, I realized that the authentication went thru okay, however, the authentication Ticket Name contained in the "RSAuthenticationHeader" has a duplicate value of "sqlauthticket, sqlauthticket". So apparently, the report server added this value twice to the response header. Has anyone seen this error before? How would you fix it? Also to bypass this error, I parsed out the header and take the correct auth ticket name, now report manager just redisplays the logon page and not letting me thru. Has anyone have seen that before? I am running in Win 2003 server, IIS 6, .NET 2.0 and SQL server 2005 enterprise version.
Thanks for the help,
Eric
I have written CDE to retrieve and process data before generating the report. It works very well inside VS.Net. However, when deployed to the report server I get the following error
An error has occurred during report processing. Cannot create a connection to data source 'CallsTaken'. Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I have added appropriate CAS entry in rssrvpolicy.config file as following
<CodeGroup
class="UnionCodeGroup"
version="1"
Name="CustomDataExtensionCodeGroup"
Description="Code group for the Custom Data Extension"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportServerinCSS.CustomDataExtension.dll"/>
</CodeGroup>
I have added appropriate entry into rsreportserver.config for data extension as
<Extension Name="Dataset Extension" Type="CSS.RS.Extensibility.CustomDataExtension.DsConnectionWrapper, CSS.CustomDataExtension"/>
Even then I continue to get this error. Any help? Is there anything else I am missing? I am using VS .Net 2005, SRSS 2005 on W23 machine.
I have been stuck here for a while now. Help is appreciated.
Thanks
Hi there,
I am implementing SSRS 2005 Sharepoint integration mode in a stand alone server and we are having problems with basic authentication.
When the MOSS web application and the Report Server application is in Windows Integrated authentication Everything works fine and we can upload and view reports in the sharepoint site.
But when we switch the MOSS web application into basic authentication and RS stays the same with windows integrated authentication the reports in sharepoint throw 401 unauthorized errors. It looks like the error occurs when accessing the _vti_bin/ReportServer/ReportService2006.asmx web service??
Anyone have any fix on this????
Does SSRS 2005 Sharepoint integration mode support Basic authentication???
Maybe someone can help me. I am running the reporting service add in for Sharepoint and we have only Basic Authentication running on our site (it is on SSL). When i try and open a report in sharepoint it errors out on me. When i go back into IIS and set Windows and Basic authentication it works, but it requires me to enter our domain and username. We need to only use Basic so it will not need the domain. Anyone have any suggestions on what i can do to make this work?
View 5 Replies View RelatedI am trying to fetch the sharepoint list data into the SSRS report.
I have given credentials in shared datasource to access remote datasource.
In preview i m getting the following error:
Data extension supports windows integrated security and no credentials only.
Is it possible to configure a datasource using data from a web service (java) requiring basic http authentication?
View 4 Replies View RelatedI'm new to SQL 7.0 and have a basic security question. I want to set up security so that different roles can see different portions of the same tables. In other words, Department A see's its projects from table 1 and Department B users see only their projects from table 1. The results are then linked to the client front end, so that the basic form they input/read off will give them the results from table 1 with only their projects. Is there a simple way to do this with a stored procedure or view, so that the result set can be linked to the form and only differs depending on the role? Thanks for the help.
View 1 Replies View RelatedHi,
I'm trying to learn the "correct" way to use security with SQL Server 2005 and .NET 2.0. Let me explain what I've always done in terms of security and why.
Historically, I've created a Users table and a UserPermissions table in my database. The Users table has the username and password, and the UserPermissions table relates to the Users table and has a list of all (application-defined) permissions granted to the user. For example, say the application has features A, B, and C. The administrator might grant features A and C to user John Doe.
The reason I put this table into my database is two-fold. First, it allows me to easily setup a list of features that the application can secure. Second, it allows the application to log into SQL Server using a constant username a password. The application always gets access to the database, but then if the user didn't provide a good username and password, the application logs back out of the database and quits.
The downside to this is that SQL Server doesn't know who's accessing it. So if I wanted to have a trigger that gets the current user, all users appear to be the same person. The upside is that the administrator doesn't need to go into SQL Server and set up new users, and the users aren't aware of the actual username and password used to access the database, so they can't get to it directly.
I'd really like to learn to set up security the right way. What I want is:
A) Each user to be uniquely identified within SQL Server - i.e., each one logs in with his own user id
B) The application has complete control over the database (I want the application to be able to read all tables, for example)
C) The user doesn't have direct access to the database. I don't want the user to be able to load SQL Server Management Studio on his computer and go directly into the database
D) There is some way to grant application-specific features to each user
I would think this has to be a common scenario. Can anyone offer some good tips on how to accomplish what I'm trying to do? Thanks in advance...
-Eric
Hi,
I am new to SQL 2005, can someone give me some details instructions about how to do below two tasks:
All my developers are in a window domain user group, I need to grant dbo privileges to that domain group so then can do the their development work. The rule is all objects they create need to be owned by dbo not by there ID. ( I can€™t do it because I got €ś The €śDeafult_Schema clause cannot be used with a windows group€?)
Same as above but this time they only need select permission on tables nothing else.
Many thanks.
PC
I'm having 2 Dummy Users.
The one i created i direct login on the server and access to 2 databases.
The second one i've placed in a AD users group.
That group has a login and is also mapped to the 2 databases.
Sql server is running with on a AD-users, not localsystem.
Why can DummyOne work fine, and DummyTwo (the grouped one) can login but access none of the databases.
What have I missed ?
I'm designing a C# winforms app that will be used by 500 customers/companies. The customers will use the app to edit data for their company on the remote SQL Server. What's the best way to manage security in terms of logins/passwords? Should each user have a different login? (probably not) Or should the application have it's own login/password that is encrypted and the user never knows what the login/password is?
Also, is it necessary to use Web Services for this application? Any other thoughts/comments are very welcome.
I need to access my SQL server from another domain across the internet. How can I do this? Everything I have tried has been unsuccessful.
Chris Walcott
I'm having a heck of a time trying to access a SQL server database using Saxon's SQL extensions with the Microsoft SQL server JDBC driver. The Saxon XSL processor has implemented some SQL extensions that allow you to access a SQL database through a JDBC driver. I have Visual Studio 2005 professional with SQL server express installed on my workstation. The SQL server express installation is installed at the default 1433 port on my workstation, it has been setup to accept connections over TCP and has been configured to accept Windows authentication and SQL authentication. In addition, I have downloaded and installed Microsoft's JDBC driver for SQL server.
After installing the JDBC driver I added the JDBC sqljdbc.jar to my Java CLASSPATH. Then I built a simple Saxon XSL that made use of the SQL extensions:
Code Snippet
<xsl:transform version="2.0"
exclude-result-prefixes="java saxon xsd xsi xsl xfn xdt xqe"
extension-element-prefixes="saxon sql"
xmlns:java="http://saxon.sf.net/java-type"
xmlns:saxon="http://saxon.sf.net/"
xmlns:sql="java://net.sf.saxon.sql.SQLElementFactory"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>
<xsl:output method="text" media-type="text/plain" encoding="utf-8" />
<xsl:preserve-space elements="*" />
<xsl:param name="jdbc.driver" as="xsd:string" select="string('com.microsoft.sqlserver.jdbc.SQLServerDriver')" />
<xsl:param name="jdbc.database" as="xsd:string" select="string('jdbc:sqlserver://localhost:1433;databaseName=prototype')" />
<xsl:param name="jdbc.user" as="xsd:string" select="string('saxon')" />
<xsl:param name="jdbc.pass" as="xsd:string" select="string('*****')" />
<xsl:template match="/">
<xsl:choose>
<xsl:when test="element-available('sql:connect')">
<xsl:variable name="sql.conn" as="java:java.sql.Connection">
<sql:connect driver="{$jdbc.driver}" database="{$jdbc.database}" user="{$jdbc.user}" password="{$jdbc.pass}">
<xsl:fallback>
<xsl:message terminate="yes">SQL extenstions are not installed</xsl:message>
</xsl:fallback>
</sql:connect>
</xsl:variable>
<xsl:variable name="sql.employees" as="element()*">
<sql:query connection="$sql.conn" table="dbo.Employees" column="*" />
</xsl:variable>
<xsl:sequence select="$sql.employees" />
<sql:close connection="$sql.conn" />
</xsl:when>
<xsl:otherwise>
<xsl:message terminate="yes">sql:connect element is not available</xsl:message>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:transform>
When I run the Java version of Saxon it issues a JDBC connection failure:
SXSQ0003: JDBC Connection Failure: The TCP/IP connection to the host has failed.
java.net.ConnectException: Connection refused: connect
Transformation failed: Run-time errors were reported
After some discussion on the Saxon listserv, I hacked a sample in the Microsoft JDBC documentation to see whether a standard Java program could use the JDBC driver and access the database:
Code Snippet
import java.sql.*;
public class connectURL {
public static void main(String[] args) {
// Create a variable for the connection string.
String connectionUrl = "" +
"jdbc:sqlserver://localhost:1443" +
";databaseName=prototype";
// Declare the JDBC objects.
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
// Establish the connection.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl, args[0], args[1]);
// Create and execute an SQL statement that returns some data.
String SQL = "SELECT * FROM dbo.Employees";
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
// Iterate through the data in the result set and display it.
while (rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4));
}
}
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}
finally {
if (rs != null) try { rs.close(); } catch(Exception e) {}
if (stmt != null) try { stmt.close(); } catch(Exception e) {}
if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}
This Java application accesses SQL server express and prints out the list of employees from the dbo.Employees table in the database:
Code Snippet
-- Create the table definition.
CREATE TABLE dbo.Employees
(
empid INT NOT NULL PRIMARY KEY,
mgrid INT NULL REFERENCES dbo.Employees,
empname VARCHAR(25) NOT NULL,
salary MONEY NOT NULL,
CHECK (empid <> mgrid)
);
GO
-- Insert test data.
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 1, NULL, N'David' , $10000.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 2, 1, N'Eitan', $7000.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 3, 1, N'Ina', $7500.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 4, 2, N'Seraph', $5000.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 5, 2, N'Jiru', $5500.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 6, 2, N'Steve', $4500.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 7, 3, N'Aaron', $5000.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 8, 5, N'Lilach', $3500.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 9, 7, N'Rita', $3000.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 10, 5, N'Sean', $3000.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 11, 7, N'Gabriel', $3000.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 12, 9, N'Emilia', $2000.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 13, 9, N'Michael', $2000.00 );
GO
INSERT INTO dbo.Employees( empid, mgrid, empname, salary )
VALUES( 14, 9, N'Didi', $1500.00 );
GO
-- Create unique index.
CREATE UNIQUE INDEX idxManagerEmployee ON dbo.Employees( mgrid, empid );
GO
Internally, Saxon uses almost the exact same code, e.g., three argument DriverManager.getConnection, but the call fails. Michael Kay, has indicated that this issue isn't related to Saxon since the error message coming from Saxon indicates that Saxon is finding the JDBC driver and issuing the connection request, but the JDBC driver is failing.
Has anyone tried to use Saxon's SQL extensions with Microsoft's JDBC driver? If you have could you indicate how you have things setup to make it work? If anyone from Microsoft's JDBC driver team is listening, would you be will to help recreate this issue or help trying to diagnose the issue?
Thanks, Andy.
Is it possible to connect an Access Project (.adp) to SQL Server 2K via the Internet?
I've installed the Office 2K SR-1 and the Access/SQL 2K Compatibility update.
I can connect to SQL 2K from any computer that is on the same subnet, but not outside of that . . . or through the Internet.
Can this be done?
Pls. respond directly or forward a copy of your response to this thread to me at flanders@mbay.net, if possible.
Thanks.
Pat
I need to develop an application in vb.net that uses sql server database so that user can access the use the application over the inter net.
How Can I accomplish that ?
What are the options ?
Pls Help..