| Edwin Daniel's profileMicrosoft technologies a...BlogLists |  Tools  Help |
|
November 30 Accessing named instances in linked serversIt's easy to execute queries against a linked server. All you have to do is specify a fully-qualified name - server.database.owner.object. So a table named Test owned by the dbo in database named DB1 in server named S1 can be referred to as S1.DB1.dbo.Test. The problem is, what if your SQL Server instances are not default instances? Let's say server S1 has a named instance N1 installed, how do you refer to the object? You use the delimited identifiers to refer to the object. In this case, it's [S1\N1].DB1.dbo.Test. The square brackets acts as your delimited identifiers for the server\instance naming convention for named instances. November 28 Accessing SQL Server 2005 behind a firewallI was working on accessing SQL Server 2005 behind a firewall. In a typical connection string from an application which accesses SQL Server, you would specify a server attribute value of instance_name:port_number. I was surprised to find out that for SQL Server 2005, you use a comma instead of a colon in this format instance_name,port_number. A sample application connection string would look something similar to this
This can be done on the infrastructure side. Simply define a DNS entry in your DNS server to point to the SQL Server instance name and the port number. Another way is to define an alias on the client network utility specifying the instance name and the port number. But developers usually do not have such privileges to work on the infrastructure side so they (including me, sometimes) have to rely on anything they can work on as far as the application is concerned. November 24 Citrix MetaFrame Presentation Server Client on Windows VistaI was forced to install Citrix MetaFrame Presentation Server Client on Windows Vista as one of our clients was requesting for a "shadow" on the Citrix Server. After logging into our Citrix server, I downloaded the Web ICA Client - Version 9.0.32649.0 and installed it on my laptop. It prompted me for some compatibility issues but I just bypassed those messages. I was able to launch the client but until now, I am still waiting for the scripts to execute so I can see the desktop. After doing a bit of research, I found out that v9.150 is the one that works on Windows Vista (although I was able to test that shadowing and remote control works on this version as well) November 23 From sysxlogins to sys.server_principalsAs I was checking for SQL Server login accounts (referring to my earlier post on accounts in 2 SQL Server instances/servers), I was tempted to look at the system tables. In SQL Server 2000, you use the sysxlogins table in the master database to check for accounts that have permissions to access SQL Server. In SQL Server 2005, this has been replaced by the new sys.server_principals table. In the sysxlogins table, the only way to find out whether an account is either a Windows or SQL account is by looking at the name column, where a Windows account has the domain\account format while the SQL account only has the account format. In the sys.server_principals table, there's the type column which identifies if the account is a SQL account (S) or a Windows account (U), and a type_desc column which gives the description of the principal type. This was my means to check whether the accounts in two servers have the same SID values or not. My database owner is gone2:00 AM, I'm at home chatting with a few friends while forcing myself to get some sleep when one of our vendors saw me online on MSN (I should close my MSN when I'm at home). They asked me to create a SQL account, let's say AccountB, that acts as the database owner for, let's say, DatabaseB. When I reached the office later during the day, we had to move the source code and the database from the Staging server to the Production server. DatabaseB, which existed in the Stagiong server, also has DatabaseB with account AccountB who is also the database owner for this database. These settings have to be the same in Production. They asked me to do a backup of DatabaseB in Staging and restore it in Production. After the restore process, I tested the database by executing a few SQL statements to check the objects. This I have done using my Windows account. I did another access test - logged into another machine, used Query Analyzer to access the database using AccountB to access DatabaseB (the database server is running SQL Server 2005). To my surprise, I can't access DatabaseB. I can log in to the database server but cannot access DatabaseB. I had server-level access but not database-level access. I checked the server and found out that the user I created earlier no longer has any permissions on DatabaseB. That's when I remembered that the restore process has overwritten them. You may be thinking that it shouldn't be the case since the same account exists in both servers - Staging and Production. Same accounts, same permissions, same everything. But computers and applications don't think that way. They do not use the "friendly" account name but rather they have their own way of checking. If you query the sysxlogins table of the master database, you will see a column name SID, which is what SQL Server looks at to validate a user account. I may have the same account name and permissions for both servers but they do not have the same SID. During the restore process, SQL Server checks the existence of this user account in the Production server and noticed that the one from the backup does not match the one in the Production server, as far as SID is concerned. That being said, it was not given permissions to DatabaseB. I just gave the account database owner permissions to DatabaseB and everything worked out fine. If you are thinking of copying the account from one database server to another, there's a script provided by Microsoft that does the trick (see my earlier blog entry on Copying accounts between two SQL Servers).
In SQL Server 2005, the SID for a login is used as the basis for implementing database-level access. A login may have two different SIDs in two different databases on a server. In this case, the login can only access the database that has the SID that matches the SID in the sys.server_principals view. This problem may occur if the two databases are consolidated from two different servers. To resolve this problem, manually remove the login from the database that has a SID mismatch by using the DROP USER statement. Then, add the login again by using the CREATE USER statement. November 16 Table Partitioning in SQL Server 2005 Part 1 (Introduction)Data accumulates as time goes by. What we do about it and how business requirements function dictate how we manage our data. Most of the time, we need to deal with very large tables with records of business transactions. This becomes a big headache when it comes to management and performance. Good thing SQL Server 2005 introduces the concept of table and index partitioning. A lot of people see this as an advantage do database management but it is more than that. Table and index partitioning introduces performance advantages that DBAs and applications can really take advantage of.
To appreciate this feature, we start by looking at scenarios. I support line-of-business applications that require records to be stored for at least 5 years. Imagine how big our databases are and how long it takes to execute a SELECT statement on a very large table (on top of that, performing backups take hours to complete). Would it be faster to execute a SELECT statement on a table containing 10,000 records compared to 100,000 records? I would assume that most DBAs would recommend that the older records be archived in a different database so that only the current records are in the database. But that is a current restriction in this scenario. How do we deal with this? The solution: Table Partitioning. We can subdivide our "very large table" into partitions so that we can do a search on the partition instead of the entire table. But in order to do this, we need to plan how we want the data to be partitioned. November 13 Reconfiguring your SQL Server disksIf you think you need to upgrade your server's hard drive, chances are that you will have to destroy the existing partition, insert the new disk in the array and recreate the partition, this time with new space allocations. If you're dealing with SQL Server and system and user databases reside on the partitions you will be changing, you will have to do a lot of stuff like performing backups of system and user databases on top of the procedures already mentioned. If you want your databases to be online while you change your disks (which I don't recommend), you can just move your database files using the the ALTER DATABASE statement. System databases should be treated differently if you need to move them to a different partition. An article from databasejournal.com by Steven Warren discusses how to do this. In my case, I did it pretty simple as outlined in the steps below. I just made sure I have the permitted downtime to do this.
Now you're ready to break the partition and insert the new disk. After doing that, you can move the files to the same partition as they were before. Restart SQL Server services and check the SQL Server logs for anything unusual. I managed to do this with both system and user databases. McAfee VirusScan Enterprise 8.0.0 does not work with Windows VistaI was trying to install McAfee VirusScan Enterprise 8.0.0 in my Windows Vista Ultimate operating system when it prompted me to check for solutions online. Looks like it is not supported on Windows Vista . . . YET! November 07 Adding a Windows Server 003 R2 domain controller in an existing domainWe are faced with a dillema. Our vendor delivered a new box with pre-installed Windows Server 2003 R2. What we plan to do with his box is to use it as another domain controller for a different site. Our existing domain is running on top of a mixed Windows 2000 Server and Windows Server 2003. The R2 release of Windows Server 2003 comes with new features for Active Directory. In order to join a domain controller running Windows Server 2003 R2, you need to do a few things. You need to update the schema on the schema master domain controller. I got a bit confused here as I thought you are modifying the schema of the domain. What Microsoft did was to simply "add" on top of the existing schema to support the new R2 features. To do this, run ADPREP.EXE /FORESTPREP on he schema master domain controller. A common mistake is running the wrong utility. The ADPREP utility is in the second CD inside the Cmpnents\R2\Adprep folder. Another ADPREP utility can be found in the first CD which is the one used for Windows Server 2003 (with SP1)
For a more detailed approach on joining a Windows Server 2003 R2 domain controller on an existing domain, check out the Microsoft Windows Server TechCenter CHECK_POLICY and CHECK_EXPIRATION option in SQL Server 2005I was working on the new security features of SQL Server 2005 by creating user accounts. One of those is the capability of SQL Server 2005 to use Windows security policies. Yuo may have already configured certain security policies in your organization and you need your SQL Server 2005 servers to inherit these policies. When creating user accounts in SQL Server 2000, we used the sp_addlogin system stored procedure. In SQL Server 2005, a new set of T-SQL statements are available to create user accounts and implement these new security features. Of course, the sp_addlogin system stored procedure still exists and can still be used to create user accounts.
CREATE LOGIN is introduced in SQL Server 2005 o take into consideration the new security features. One of them is to use the existing security policies of the Windows operating system. The CREATE LOGIN statement has the following syntax:
The options I would like to emphasize on are the CHECK_POLICY and CHECK_EXPIRATION options. The CHECK_POLICY option is turned on by default, even if you create an account using the sp_addlogin system stored procedure. It is used only for SQL Server accounts (as Windows accounts will automatically implement security policies). This makes sure that the password policies on the Windows server which SQL Server 2005 is running will be applied, such as password complexity, minimum password length, etc. The CHECK_EXPIRATION option specifies whether password expiration policies should be applied to the account. In order to use these options, your SQL Server 2005 must be running on a Windows Server 2003 platform (or later should Windows Longhorn comes out). I created a sample account which uses these features using the statement below
One more option that I added is the MUST_CHANGE option which prompts the user for a new password the first time the account is used. After creation, I tested the account by logging in using Query Analyzer and SQL Server Management Studio. When I used SQL Server Management Studio, it prompted me to change my password, similar to that of Windows, with this message "Your password is expired. You must enter another password before you can log on." Using Query Analyzer, I got an error message
The error is descriptive but beyond the prompt, the user cannot do anything except use SQL Server Management Studio to change the password. These new security provide additional means to secure SQL Server 2005 and have an option to implement a uniform security policy across your organization. For a detailed description of the CREATE LOGIN statement, check out SQL Server 2005 Books Online |
|
|
