SQL Server 2005 Authorization Advice && Configuration
Mar 20, 2007
Does anyone have a good starter's tutorial on authorization advice & configuration for SQL Server 2005?
I need to know what is adviced what web users can specifically do and dont in my db, and how to configure that...
I'm looking for configuration advice for a SQL Server. I have thefollowing hardware to work with:1 Dell P4 2.66 GHz PC with 512 MB RAM and a 40GB IDE drive2 Seagtate Barracuda 4.55GB SCSI drives (ST34572W) in an external bay1 Adaptec 2940UW SCSI ControllerWindows 2000 Advanced ServerMSSQL 2000 EnterpriseThe PC will strictly be a SQL Server only - no IIS or FTP or otherservices. It will host about 8-10 databases that service a departmentof 300 people. My plan is to install W2K and MSSQL on the PC's IDEhard drive and configure the identical SCSI Barracudas as a mirroredvolume to host the databases (MDF/LDFs).Will there be any performance problems installing MSSQL Server on theIDE drive while the MDF/LDFs are on the SCSI drives? From aperformance standpoint, would I be better off installing MSSQL Serveron the SCSI drives?
When i try to view in IE browser I am required to enter credentials. I enter Admin user and password but this does not work - not authorized to view page.
Looked in IIS -> ReportServer$SQLExpress and would have expected to see report here or in Pages folder. It is not there!
Hi Guys Hoping that someone out there is able to give me some help on this one
I am trying to install SQL Server 2005 developer Edition on to a new laptop that is running Windows vista home premium edition
However after running the install and then trying to run the Service Pack 2 it producing an error saying that the windows authorisation is not valid and not using named pipes protocol. After some searching and googling I have checked all the option that I can see like that my login to the windows environment is the administrator which its is but from there I still am unable to log
Any Ideas people, or is this a known problem that I am just not seen
I've been using SQL Server 2005 Std. for my development work on a laptop w/ 2 GHz Celeron and 768 Mb RAM. Now that Express Edition has Reporting Services and Full-Text Search, would I be wise to just use it instead? I actually have both on my machine since Express Edition installed with VS 2005 Standard. Also, if I uninstall SQL Server 2005 Std. what will I lose? Thanks in advance.
I am not new to the SQL Server world, but am somewhat new to the SQL Server 2005 world. I am just getting into the reporting tool. By just messing around for about an hour or so, I was able to get what I wanted somewhat. Basically, I'd like to get up to speed on the reporting side of things fairly quickly. So I am looking at some books, and was wondering if anyone had any suggestions on any.
Here is a few that caught my eye:
-MS SQL Server 2005 Reporting Services by Brian Larson -MS SQL Server 2005 Reporting Services for Dummies -Professional SQL Server 2005 Reporting Services by Paul Turley -The Rational Guide to SQL Server Reporting Services
I am thinking the 1st two would probably be my best bet, but wanted to get you folks suggestions.
Let me know as soon as you can as I am planning on going to the book store tonight or this weekend.
I am in the planning phase of building a Form Authenication Report Server project. The database contains the Role information for each report and for each user. The objective is to authenicate and authorize user so that only the authorized reports are available for the current user to view.
Base on my research these are my plans.
1. Deploy Reports onto the Report Server 2. Put Reports in folders to group different types of report (I am not sure is it possible to only the display authorized reports for the current user) 3. Design the custom Security extension to authenicate and authorize using the database
1. Is it possible to only display authorized reports for the current user to view by doing custom security extension?
I have a vb 6 program that has been running for several years against a 6.5 db. I convert the db to 2000, and now when I repoint the software to the 2000 db, I get "Invalid Authorization Specification" when I try to open the connection. The connection string is:Dim conn As ADODB.ConnectionSet conn = New ADODB.Connectionconn.Open "Provider=SQLOLEDB" & _ ";Data Source=" & sServer & _ ";Initial Catalog=" & sDB & _ ";User Id=" & goProcess.UserID & _ ";Password=" & goProcess.Password & ";"All of the variables are filled out correctly, when I go against a 6.5 it works, Sql Server 2000 doesn't.
I am working on creating a configeration database based on the information pulled from a large number (over 40) of servers. I am newly out of college and don't have a lot of experience with things like this, but I am trying to create a configuration database. I need to know the best way to create a connection to all of the SQL servers on my network and then automate the pulling of data from these SQL servers. The types of data I would need are things like Instance ID, DBMS Version, Database Properties, Database Name, Modification Date, Last full backup, last transaction log backup, etc. I have been pointed towards either working with OSQL/SQL CMD, in which case could someone please point me towards a guide, and SSIS, I am reposting this thread in the SSIS area. I need to take all of the gathered data and place it into a central repository database after formatting it. Any help is greatly appreciated.
A couple of small questions for anyone who can help. I am writing an application and am a newbie with SQL Server 2005 Express Edition and Visual C# (using Visual Studio 2005 pro).
My application will need to run on 6 different PC's at one time with our office server hosting the database. Since the server is hosting the database, is it necessary for the individual PC's to have SQL Server 2005 Express Edition installed on their hard drives also to communiucate back to the server?
Also, where can I find some nice example code for connecting and performing some simple tasks under C#?
Hello,I am trying to connect to a DB, which is hosted on an SQL Server 2005 by a provider, using ASP.NET Configuration Tool. The result is the following error when I click on the Security Tab:---There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---The hosting provider checked that my connection string is correct. They do not know why I get the error, though. Here is a relevant snippet from my web.config:<connectionStrings> <remove name="LocalSqlServer" /> <add name="LocalSqlServer"connectionString="Data Source=ABC;Integrated Security=false;Initial Catalog=ABC;User ID=ABC;Password=ABC"providerName="System.Data.SqlClient" /></connectionStrings>ABC is a mock :)Could anyone pls help? It is much appreciated.
Hi!I have installed Sql Server 2005 Express on my machine. I want to access this server from another machine in LAN.Also installed Sql Server 2005 on another machine. Allow Remote Connections is selected with Sql Server Surface Area Configuration.Please suggest the correct solution. (in detail) Thanks in advancemilind
Sorry for the annoying. I would like to the configuration settings for the surface area. There's an option which able to let us change computer (whether is local computer or remote computer). Let say I put my own computer's name (Hans as example) for configuration as the remote compute, it's work fine to me. However, if I put my remote computer's name, it displays this error message to me.
Error :
Computer Tomato does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required Windows Management Instrumentation components and then try again.
I am sure that the remote computer is working since I am able to ping the remote computer and test on telnet as well. But why it seems that surface area can't detect my remote computer. Hope can get some resistance from everyone here. Thanx alot.
hi ; everybody i have almost installed sql server 2005 enterprise edition on windows xp sp2 and i havent connected to any server .i have taken error:40 with my all attempts.tcp/ip protocol have been enabled on sql server configuration manager.i have tried to start sql server surface area configuration but every my attempt have been resulted a fault which is below. [No SQL Server 2005 components were found on the specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLSAC)] i dont suppose that i did something wrong when i setupped.because i read every article on the internet about how to make setup.also; i setupped numbers of times on my pc. but i didnt accomplished. i thank for any help.please; give your solution explicitly.
Hi.I have an ancient preCambrian app that uses the db library to interface withsql server databases.It is written in c++.When testing it I noticed on one machine with sql server 2005 installed itruns fine, but on another it has some problems, notably.. it gets as a resultof a select statement with an order by clause the error:only fully keyset ddriven cursors can have 'order by', 'group by', or 'having'phrases.Error code 10080Can someone suggest a 'configuration' parameter that might be causing theotherwise identical systems to have this problem on one machine and not theother?ThanksJeffthanksJeffJeff Kish
I plan to migrate a SQL 2000 instance with everything in it to another SQL2000 server, and eventually in the future to SQL 2005.
I've pretty much figured out how to migrate everything execept the linked server configuration (there're many linked servers configured in the old server with different security credentials). I'm wondering if there's way I can save the linked server configuration and restore it to the other server.
I am new to SQL server 2005 and have a config question:
I am controlling database connection info using XML indirect config - no problems there.
Essentially I am going to have a number of packages that need to use a common file path, that might change from one server to the next, e.g. Server 1: C:sourceFiles versus Server 2: D:sourceFiles. Within this directory the filenames will remain static. So in the flat file connection manager I'd like to use a variable to reflect the folder - but I don't want to have to create this for each package.
So, I thought I would create a system environment variable and create expressions for the connection managers - something like %SOURCE_DIR% + "file.csv" - but this does not evaluate correctly.
So then i though I could use the SQL server configurations table with a configurationFilter SOURCE_DIR and appropriate configuration value - but then how do I access this in the flat file connection manager to create a dynamic file name?
So essentially I want a variable/property available globally to all my packages and potential flat file connection managers that help me to centrally control file path locations.
I cannot see any services within "SQL Server 2005 Services" in SQL Server Configuration Manager.
I am new to SQL Server 2005 and recently installed all of the SQL Server 2005 64-bit components on to a Windows 2003 64-bit server. I can create and view databases via SQL Server Management Studio. I can also start and stop the SQL Server service for the default instance via SQL Server Management Studio. But I should be able to start/stop the service via SQL Server Configuration Manager and I believe I'm supposed to use SQL Server Configuration Manager to make changes to the services.
I'm concerned that something may have become corrupted and is effected the SQL Server Configuration Manager. Any suggestions?
I have tried several times to install SQL Server 2005 Developer Edition (most recently from MSDN Disk 3097.1) I have tried to complete an uninstall before each attempt.
I do get the warning about Minimum Hardware Requirement, but can find nothing in the detail requirements to exclude my PCs configuration.
I have installed: IIS and .NET Framework 2.0
I have also used ..aspnet_regiis -r to register ASP.NET
I am, successfully, running Visual Studio 2005.
Why does the installation process stop there and what can I do next?
I have just installed SQL 2005 enterprise and every apeared to install ok. But using SQL config manager nothing is showing up under SQL 2005 Services tab.
Hello. I am called Narsiste. I have a problem of configuration in SQL server 2005 express train. In fact, I do not know how to make the configuration to tackle the databases SQL server 2005 express train which are on a station has starting from a station B (both being in a network LAN). But I read in the module of €śconfiguration of the surface of exposure for the services and connections - localhost€? that €śBy defect, the editions Express train, Evaluation and Developer SQL SERVER 2005 authorize only local connections.€?. As it is BY DEFECT, I said myself that it will have to be changed a parameter so that access TCP/IP can go on this version of SQL SERVER.
Here is the message which I have: €śAn error occurred during the establishment of a connection to the waiter. At the time of connection to SQL Server 2005, this failure can be due to the fact that the default settings of SQL Server do not authorize remote connections. (Provider: Interfaces network SQL, error: 26 - Error during the localization of the waiter/the authority specified) (Microsoft SQL Server, Error: -1)€?.
If somebody encountered this problem in the past, that he wants to inform well me of the solution which he found for this last. Thank you.
I have just finished installing SQL 2005 Ent Edition on Win 2000 Adv Server, SQL2005 SP2, and SP2 Hotfix KB934458. After the installation, I could see and configure all services via SQL Configuration Manager and SQL Server Surface Area Configuration tools. This worked for a couple of days and now both configuration tools no longer detect SQL2005 components. SQL Server Surface Area Configuration issued an error that said "No SQL Server 2005 components were found on specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLAC)". SQL Configuration Manager did not list any installed services. I don€™t know what caused this. Anyone has any idea? Please help! Below is the Installation Report which shows installed components.
===================================
The following components are installed on this server
Hello allMy computer was installed with SQL Server 2005 Developer version, however, I checked the Default Web Site in Internet Information Service console and saw that the ASP.NET configuration was not correct as below."data source=.SQLEXPRESS;Integrated Security=SSPI; AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"I have a question that can I replace the string, “.SQLEXPRESS”, with my server name and my SQL Server 2005 Developer named instance as below“DEVSERSQL2005Dev”?Thanks in advance for your help.
I have problem during first configuration of Reporting Services from MS SQL 2005 Server Express with Advanced Services SP2. The error occurs when configuring virtual directory (Reporting Services Configuration Manager -> Report Server Virtual Directory). When I try to create new virtual directory I can see following messages:
Number of errors: 0 Create Virtual Directory
The virtual directory was created and assigned. Changing Secure Connection Level
After last message Windows reports that SQL Server 2005 Reporting Configuration Tool has encountered a problem and needs to close (and I can send report to Microsoft).
IIS shows that virtual directory under default web site was created.
My environment: MS SQL Reporting Services Version 9.00.3042.00 Windows XP Proffessional SP2 IIS 5.1 all components are fresh new installations. The only additional configuration I've made was adding IWAM_<hostname> as WebServiceAccount, all other configuration is default.
I have Microsoft Office Professional installed. It came with SQL SErver 2005 but I am not sure how to configure it. It is running( the SQL Server under Windows Services) but I can't find the MMC console to administer it. I tried linking from Access to create a SQL Server datasource but the SErver does not show up in the dialog box. Under Program Menu/SQL Server there is a Configuration Tools folder with the SQL Server Configuration manager. Is this the same as the MMC? My SQL Server is on my local computer with the Access Database I want to upsize. There are 3 or so folders under SQL Server Configuration Manager . SQL Server 2005 Network Configuration and SQL NAtive Client Configuration. I don't know what I'm supposed to do at this point but what I would like to do is add my user name and password as the administrator and find the MMC console. I would like to create a datasource in Access to the SQL Server. I am not sure whether I need the client or not since the SErver is on the same computer as the database. This is only a test run.
I would appreciate getting unstuck. Do I also need to install the MMC separate from the SQL Server. Since it installed when I installed Office Professional I didn't go through the steps of installing it and somethings seem like they are missing.
Hi, I want to call a dll from Stored procedure developed in SQL Server 2005 at configuration level 80. but when I execute the stored procedure I get the following error.
Error Source: ODSOLE Extended Procedure Description: Invalid class string
Code of stored procedure and vb.net class is given below:
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String) ' impersonate the calling user Dim newContext As System.Security.Principal.WindowsImpersonationContext newContext = SqlContext.WindowsIdentity.Impersonate() Try Dim w As StreamWriter = File.AppendText(LogName) LogIt(newMessage, w) w.Close() Catch Ex As Exception
Finally newContext.Undo() End Try End Sub End Class ===============================================================
STORED PROCEDURE Create PROCEDURE [dbo].[PPGenerateFile] AS
BEGIN Declare @retVal INT Declare @comHandler INT declare @errorSource nvarchar(500) declare @errorDescription nvarchar(500) declare @retString nvarchar(100)
-- Intialize the COM component EXEC @retVal = sp_OACreate 'PositivePay.class', @comHandler OUTPUT IF(@retVal <> 0) BEGIN --Trap errors if any EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT SELECT [error source] = @errorsource, [Description] = @errordescription Return END
-- Call a method into the component EXEC @retVal = sp_OAMethod @comHandler,'LogToTextFile',@retString OUTPUT, @LogName = 'D: ext.txt',@newMessage='Hello' IF (@retVal <>0 ) BEGIN EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT SELECT [error source] = @errorsource, [Description] = @errordescription Return END select @retString
Help. I have been tasked with upgrading a 2000 instance to 2005.I have actually done this before, but it was a long time ago, and Ididn't do it alone. Now, the sitation is little different, and I needto know the EXACT steps to take.Does anyone have a FAQ or link that outlines migration steps? I foundone on sql server central, but it isn't very detailed.One of the important things I need to know is, how do I create arollback plan if I am upgrading from 2000 to 2005 on the same server(instance)?Also, why can't I seem to find a comprehensive list of TO DO's whenupgrading? Doesn't microsoft provide this? You would think so. I willrun upgrade advisor first, but isn't there also documentationsomewhere?I seem to recall lots of permissions issues that arose with 2005.HELPThanks
We are trying desperately to get away from Access (ADP) and move on to something else. SQL Server 2005 is the engine and Access is the front end. We desperatley need another front end. We torn between FoxPro or going Web Bassed. Can whats a good application tool that will asssit me in making our Databases webbased??
I am new to SQL 2005 CE but fairly experienced with SQL 2000 and know my way around SQL 2005. I am creating a PDA app that must support the following scenario:
My company has a SQL 2000 server with a "computer assets" database. This asset database must be able to be transferred to an .sdf file (SQL 2005 mobile) on the PDA when the user docks their PDA to the desktop PC (not the same machine as the SQL 2000 server). This transfer can be via an option in the mobile app that is initiated by the user, or automatic.
The mobile application basically supports stock-taking of the assets, so users go out on site and record information about the assets. This information is stored in a separate .sdf database on the PDA.
When the user gets back to the office they dock with their desktop and then must transfer the data from the second .sdf database onto the SQL 200 server. Lets call this the "asset metadata".
thats basically it! but im having issues finding the right process to attack the data transfer tasks.
My issues:
- Firstly, how do i populate the .sdf on the PDA for development purposes? Microsoft tutorials seem to be geared at SQL server 2005 management studio only. I only have SQL server 2005 Management Studio Express on my development machine as well as all the necessary SQL 2005 CE SDKs and VS2005. We do not use SQL Server 2005 at my organisation as yet and it will be some time before we migrate across. In effect im looking for something that behaves like a DTS package to transfer data across.
- How to populate the "asset database" .sdf file from the SQL 2000 server when the user has docked to their desktop PC? (This needs to be refreshed )
- How to transfer the "asset metadata" from the .sdf on the PDA to the SQL server?
ive looked at merge replication a little but am not sure whether this is what i need and again most online information seem to be tailored to SQL Server 2005.
Can anyone kindly point me in the right direction?
Hello everyone, I am upgrading from SQL Server 2000 to SQL Server 2005. Any caveats? Can I just detach the db's and attach them into 2005. Or is there any conversion I should run or import first?