I'm new in the DBA position recently aquired, and I'm having a few issues with MSSQL configuration and administration.
1. I have created a two maintenance plans, one for system databases and another one for user databases, this plans include things such as, Integrity Checks, Optimizations and DB Backups, however the Integrity Checks fail with the error that the DB's must be in single user mode, how can I go about to make this and what are the implications of doing so?
2. There's been a few reports of MSSQL server compromises through UDP port 1433, so I closed it at firewall level, only TCP 1433 is going through, do I really need UDP 1433?
Is it possible to write an ASP.NET app that can do certain SQL Server 2000 administration tasks, specifically add sql server users, and change sql server user passwords? Would it just be a matter of running the appropriate system stored procedures (sp_password, etc.), or does .NET provide any hooks into SQL Server for something like this? Thanks!
Sorry for starting a new thread on this subject but my situation is a bit different.
I want to connect to a remote SQL-server in a DIFFERENT domain, using Enterprise Manager. Whatever I try, I allways get an "SQLserver not found"-error.
The ASP's on the server that make use of the SQL server are functioning without any problems, so I guess the SQL-server itself is OK.
I tried: servername = URL remote server setting up a linked server setting up a remote server
I'm a webdesigner and a newbe to SQLserver7. Please keep in mind I could be overseeing something that very obvious for you.
I'm searching for some alternate administration tool (permisions, rights,etc.) on sql server, something not so complicated as default. Does anyonehave some info about that? ThanksPete
Hi All, I will shortly (hopefully) begin devleoping an ASP.Net web application to let users manage a remote sql server 2005 database (kind of like the php myAdmin tool for MySQL databases). Were not talking a server managament tool for DBAs, more an end user tool for managing personal databases (it seems that web hosting companies provide tools for MySQL, but if you use SQL Server, you're stuck unless you have management tools on your PC - and what about mac users?).Given that premise, what features should i attempt to include? Creating and modifying tables structures and data is an obvious inclusion, but what else? I also haven't yet decided whether to go about this using scripts or using SMO. I've read that SMO is backwards compatible across versions of SQL Server...how true is this? are there issues or is it seamless? Please post any comments or thoughts you have, and thanks for taking the time.
I am new to VWD, and web development. I installed VWD and SQL Server Express local.
I'm learning how to use the VWD with some of the walkthrough samples. It was going great until I got to the walkthrough where it show "how to use configure user and role with Web Site Admin Tool". I followed the walkthrough steps by step. I clicked the Security Tab and I got the following error:
"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: Unable to connect to SQL Server database. "
I read some article. According to the articles, SQL Server Express will automatically create the asp database for the first time. but my SSE did not create the database.
I am an Oracle DBA with over 7 yrs of experience.I am new to sql server 2000 and am given the responsibility of sql server 2000 production databases in a few weeks.I already have the sql server 2000 DBA survival guide.
I would like to know if there are any good books out there for
I wanted to know if there's a limit on how many targets I can manage in Multi Server Administration.In addition, are there any known limits regarding managing SQL instances with different versions (SQL 2005,2008,2008R2 and 2012).
I am running SQL Server 2005 Standard Edition with SP2 on Windows XP professional. When I check the microsoft documentation, it says that it is not possible to setup multiserver administration in standard edition. Have anyone tried this in Standard edition.
I installed SQL Server 2005 Express Edition, I can login to it just fine using SQL Server Management Studio Express. I ran the aspnet_regsql and it created the aspnetdb database. But when I try to test the AspNetMembershipProvider or go to the Security tab in the ASP.NET Web Site Administration Tool it does not work. I have tried messing with the connectionString of the LocalSqlServer in the machine.config, but to no avail. My SQL Server is installed on the Default Instance. Can someone help me get ASP.NET Web Site Administrator to work with my SQL Server 2005 Express Edition?
I'm not quite sure where to post this question, so I'll try here. We've been struggling getting SQL2005 reporting services integrated with Share Point Services 3.0 for several days now.
We've installed SQL Reporting Services with SP2 and run the SharePoint Reporting Services add-in. We've been able to go through the SQL Reporting Services Configuration and now were at the last step in the SharePoint Application Management Reporting Services configuration.
The first two steps of Manage Integration Settings and Grant Database Access was sucessful. The final step of "Set Server Defaults" gives us a 404 error message. There is nothing in the Event Log to suggest that there was a problem, and the SQL2005 Server is in SharePoint Integration mode. I've searched but can't find a solution. Can anyone steer me in the right direction please?
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
I have a query. I want to use sql server authentication for my asp.net application. I have created a seperate username and password for the sql server. Now I want to use that username and password to establish the connection thru web.config. In web.config, I don't want to give the username and password of sql server. Instead, I want to get those details in other ways. Please tell me how to handle the connection string with more security?
Greetings, My connection string looks like this (in the web.config) and is used with many data controls in my application: <connectionStrings><add name="connection_NAH" connectionString="Data Source=NAH;Initial Catalog=NAH;User ID=xxxx;Password=xxxx" providerName="System.Data.SqlClient" /></connectionStrings> I'm using vb.net and my code looks like this:Dim myConnection As SqlConnectionDim mySqlCommand As SqlCommandmyConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("connection_NAH").ConnectionString)mySqlCommand = New SqlCommand("UPDATE tbMasterOption SET ImageURL = 'test.gif' WHERE ID = 116977")Try myConnection.Open() mySqlCommand.ExecuteNonQuery()Catch ex As Exception Label3.Text = "Couldn't update database record: " + ex.ToString()Finally myConnection.Close() Label3.Text = "Closed db connection"End Try ------------------ The only output I get is from the label3 which says "Closed db connection". Doesn't look like any exceptions are thrown but when I look at the database the record is not updated. Originally I thought my SQLConnection was not constructed correctly but I also tested it by outputting the connection string to Label3 which looked fine. The only other thing I can think of is in the use of ExecuteNonQuery(). Is this the right method or do I need something else? Running the sql query manually works... Thanks in advance..
I'm using a package configuration to set the value of a user variable which I then use inside my package to build out a certain expression. I got this to work with the XML file option, but I can't get it to work using the SQL Server option.
So this is what Im doing.
(1) I copy pasted the package that works with the XML config option. (2) In the new package, I deleted the existing XLM config and created a new one, this time choosing the SQL Server option. After I finish, I see that the dbo.SSIS_Configurations table gets created in my database with the value of my variable in there. (3) I run the package and I see that it works. I'm not sure at this point if it is working because it's reading the value from the config table or from the BIDs environment where I initialized the value. (4) Anyway, I then go and update the value of my variable in dbo.SSIS_Configurations. (5) When I run the package again, it doesn't see the new value. What am I doing wrong?
I'm trying to configure ASP.NET2.0 Application to use SQL Server 2000 and yes i did make sure all my setup in configuration manager is setup and the surface area Whenever i ran the REGSQL .EXE CMD it works until i get to where it suppose to give you a list of data instance to choose from base on ScottGu article in step 4 I'm running SQLEXPRESS ADVANCE VWD EXPRESS 05 Thanks
Hello, So I am just getting started with .NET, I am using Visual Web Developer 2005 Express andSQL Server 2005 Express on XP Media Center (same as XP Pro) ASP.NET version 2.0.????Anyways, I am having the time of my life figuring out how to connect to SQL Server from within my C#, as follows,(where settings.connection = ".SQLEXPRESS;Database=MyDatabase;Integrated Security=SSPI;" in my web.config)SqlConnection conn = new SqlConnection(Settings.Connection); SqlDataReader rdr = null; try { conn.Open(); SqlCommand cmd = new SqlCommand("select * from mytable", conn); rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(rdr[0]); } } the above yields unspeakable errors, namely <font color="red"><b>Cannot open database requested by login. login failed for user 'computername/SQLEXPRESS'</b></font> , etcAND<font color="red"><b>A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)</b></font> If you have ANY information on the suspect, please, please report it to me. Greatly appreciated!Thanks, Cam
any suggestions on having one web config that once put on the dev servers uses the dev sql server and when put on prod will use the production sql server? would like to encrypt it and be done with it. but it needs to recognize the server it's on. I have a connection class that does this - but i need to use sqldatasource and not objectdatasource.
Which will perform better? (4) 550Mhz Xeon with 1MB cache and 2GB of RAM (2) 550Mhz Xeon with 2MB cache and 2GB of RAM (2) 550Mhz Xeon with 1MB cache and 4GB of RAM
When I installed MS SQL Server 2000 on our client's computer I installed it under a local account using Windows Authentication as the server logon and am now accessing it using a network login (remoting into the same box). The result is that if I change the password for the local Windows account and reboot the server, SQL server cannot be restarted; I get an error:
A connection could not be established to (LOCAL)
Reason: SQL SERVER does not exist or access denied. ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registration properties and try again.
I've changed the registration properties to use SQL Server Authenticaiton instead of Windows to no avail. Whichever connection option I use the server will not start unless I change the local account's password back to what it was when I installed. This works, but our client wants to have that local account's password changed for security/peace of mind. Any help or advice would be appreciated here :)
I have been developing a SQL based data warehouse for financial reporting purposes. I do expect the database to continue to grow over the next few years.
Right now, I am running SQL Server 2000 on an HP DL380 G3 with dual Xeon 2.8GHz, 1.5GB of ECC DDR RAM, 4x 300GB SCSI 10K RPM HDD's.
I would like to replace the server as it is now two generations old and because when there are five people running queries against the data, it starts taking minutes to return the results. When this reporting database hits production, it will be queried by up to 20 people simultaneously and I don't feel the current server will provide results in reasonable amounts of time.
I would like to know what you people think I should be doing to provide an optimal environment for this data warehouse. Should I stick with SQL Server 2000 or move to 2005? Why would you recommend that?
I am thinking of replacing the server with the following configuration: HP GL380 G5, 2x Xeon E5345 Quad-core CPU's, 4GB PC2-5300 ECC DDR2 RAM, 8x 72GB SAS 15K RPM HDD's. Do you think this is an ideal configuration? Too much? Too little?
I would love to hear what you have to say and suggest. Thank you in advance!
I have been developing a SQL based data warehouse for financial reporting purposes. I do expect the database to continue to grow over the next few years.
Right now, I am running SQL Server 2000 on an HP DL380 G3 with dual Xeon 2.8GHz, 1.5GB of ECC DDR RAM, 4x 300GB SCSI 10K RPM HDD's.
I would like to replace the server as it is now two generations old and because when there are five people running queries against the data, it starts taking minutes to return the results. When this reporting database hits production, it will be queried by up to 20 people simultaneously and I don't feel the current server will provide results in reasonable amounts of time.
I would like to know what you people think I should be doing to provide an optimal environment for this data warehouse. Should I stick with SQL Server 2000 or move to 2005? Why would you recommend that?
I am thinking of replacing the server with the following configuration: HP GL380 G5, 2x Xeon E5345 Quad-core CPU's, 4GB PC2-5300 ECC DDR2 RAM, 8x 72GB SAS 15K RPM HDD's. Do you think this is an ideal configuration? Too much? Too little?
I would love to hear what you have to say and suggest. Thank you in advance!
I'm trying to get a SSIS package to run as a SQL Server Agent job. The package uses a config file. When I try to add the config file to the job under the Configurations tab. When I click on the 'Add' button, I get the following error:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The EXECUTE permission was denied on the object 'xp_availablemedia', database 'mssqlsystemresource', schema 'sys'. The user does not have permission to perform this action. The statement has been terminated. (Microsoft SQL Server, Error:229)
As we know Sql Server 2005 provides its ability to deploy clr assembly on it. However my problem is that my assembly has a configuration file. I used ConfigurationManager.GetSection to create some of the object. When I am deploying the project the trouble is that the app.config is missing. I used a class lib project type to create the assembly. Besides changing a storing method, can I still use this config file to reach my goal? Sqlserver.exe.config is not a good idea and it is not workable. The sqlserverexe host cannot find the assembly files.
Firstly I should say my Reporting Services were working fine until I changed a setting in IIS today - under the Virtual Directory for the site I clicked Properties>ASP.NET>Edit Configuration. Changed 'Authentication' to Forms and everything went wrong; changing it back to Windows made no difference.
Now when trying to navigate to Report Server / Report Manager I get these errors:
RS : An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help Object reference not set to an instance of an object. RM: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
Following advice found elsewhere on the web, I checked my web.config file for an an erroneous xmlns entry... but it wasn't there. I have also deleted/recreated my virtual directories from within IIS/RSC. Also I have done a basic reinstall of Reporting Services (from within Add/Remove Programs>Change).
Below are my error logs and the listings for my web.config files; hopefully these will be of use in working out where the problem lies - I really need to get this fixed as soon as possible.
Thanks in advance, Dan
SQLDmpor####.log
w3wp!library!1!11/30/2007-20:22:40:: i INFO: Exception dumped to: d:AppsSQL2005MSSQL.2Reporting ServicesLogFiles flags= ReferencedMemory, AllThreads, SendToWatson w3wp!library!1!11/30/2007-20:22:40:: 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.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.ReportingServices.Library.WebConfigUtil.GetWebConfigAuthenticationAttribute(String attrName) at Microsoft.ReportingServices.Library.WebConfigUtil.GetAuthenticationType() at Microsoft.ReportingServices.Library.WebConfigUtil.get_UsingWindowsAuth() at Microsoft.ReportingServices.WebServer.Global.EnableAntiDos() at Microsoft.ReportingServices.WebServer.Global.Application_EndRequest(Object sender, EventArgs e)
When clicking to open SQL SERVER CONFIGURATION MGR I recieve the following....
MMC cannot open the file C:WindowsSystem32SQLServerManager.msc This may be because the file does not exist, (it does) is not an MMC console, or was created by a later version of MMC This may also be because you do not have sufficient access rights to the file.
