SQL Server MDS Staging structure rename or _1_leaf tables discovery

Did it happen to you?


Working on MDS for a new customer, writing staging scripts and not able to see the matching _leaf table and staging stored procedure?


When renaming entities in MDS up-until SQL Server 2016 it was not really supported to rename the staging structure as well. (https://msdn.microsoft.com/en-us/library/ff486987(v=sql.130).aspx)


So you could have an entity "IaMEntity" without the "stg.IamEntity_leaf" staging table.


There are scripts out there that make it possible to rename your staging structure in MDS before getting to SQL Server 2016. Bu why do it when it's not supported? [Edit: okay it's fun to figure it out and better to hand over to new ppl]


MDS is not really a very clear structure so what is happening? Just to make it bit easier I just use below query to discover the correct staging base for an entity.


SELECT
[M].[Name] AS [ModelName]
, [M].[MUID] AS [ModelMUID]
, [E].[Name] AS [EntityName]
, [E].[MUID] AS [EnityMUID]
, [E].[LastChgDTM] AS [EnityLastChgDTM]
, [E].[EntityTable]
, [E].[SecurityTable] AS [EntitySecurityTable]
, [E].[StagingBase] AS [EntityStagingBase]
FROM [mdm].[tblEntity] E
INNER JOIN mdm.tblModel M
ON (E.Model_ID = M.ID
)
ORDER BY
[ModelName], [EntityName]

Execute Permission Denied on the object sp_send_dbmail when running a SQL Agent job

Who never did get the following error when running a SQL Agent Job (SQL Server 2008 or higher)?

Message
Executed as user: <user> The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229).  The step failed.

Current at my customer after moving some databases and SSIS packages to an other server we did get this error again.

The user that was specified as Run AS user in the SQLAgent Job is already a security principal in the MSDB database and even member of the MSDB databaserole: DatabaseMailUserRole
You can check this by query:

SELECT
T2.name
, T3.name
FROM sys.database_role_members T1
INNER JOIN sys.database_principals T2
ON (T1.role_principal_id = T2.principal_id
)
INNER JOIN sys.database_principals T3
ON (T1.member_principal_id = T3.principal_id
)
WHERE
T2.name = 'DatabaseMailUserRole'

This role grants execute rights on the StoredProcedure sp_send_dbmail so the <user> is allowed to execute. You can check this by query:

SELECT DISTINCT
T1.name
, T1.type_desc
, T2.class_desc
, T2.permission_name
, T2.state_desc
, CASE
WHEN T4.type_desc IS NULL OR T4.type_desc = 'SYSTEM_TABLE' THEN T2.class_desc
ELSE T4.type_desc
END
, Isnull(T3.name, Object_name(T2.major_id))
FROM   sys.database_principals T1
       INNER JOIN sys.database_permissions T2
               ON T2.grantee_principal_id = T1.principal_id
       LEFT JOIN sys.schemas T3
              ON T2.major_id = T3.schema_id
       LEFT JOIN sys.objects T4
              ON T2.[major_id] = T4.[object_id]
WHERE  T1.type_desc = 'DATABASE_ROLE'
       AND T2.class_desc <> 'DATABASE'
AND T1.name = 'DatabaseMailUserRole'

So why still this error?

Some additional information, in the SQLAgentJob there is an step that executes as StoredProcedure "usp_SendETLAuditMail" that is created in the database DWHFrameWork.
This StoredProcedure just collects some audit information and a list of endusers and ETL developers that need to receive an message when the SQLAgent Job is executed. In this StoredProcedure the sp_send_dbmail is used by EXEC msdb.dbo.sp_send_dbmail......

In the SQLAgent Job step the database is set to DWHFrameWork and thats the clue.... the user has permission but the database that is holding the StoredProcedure isn't trusted....
SELECT is_trustworthy_on FROM sys.databases WHERE name = 'DWHFrameWork' shows 0

I don't know an other way yet but my solution for now is simple:
ALTER DATABASE DWHFrameWork SET TRUSTWORTHY ON;

And all is working fine, but is this really secure?

SQL Server 2014

Hi,

Are you certified on SQL Server 2012 yet?
Hurry because SQL Server 2014 is comming.

http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx

Remember

Hi,

This is just a blog for me to remember those commands I keep forgetting, or mistakes I keep making!

findstr /m /s /n /i /c:"<text>" * = Find <text> in all files and subfolders and display the filename that has the text in te file

Ofcourse if you like to add your quick wins drop a reply...

_msdcs could not be resolved

Today I was working on setting up my new test environment based on Windows 2012, SQL Server 2012 and SharePoint 2013. On my Windows 8 Notebook in Hyper-V, 32GB, i7, 128GB SSD BootDisk & 512GB SSD disk to store my virtuals. But this had nothing to do with the mistake I made, just wanted to let you know what I working with.

I started with a new Windows 2012 Domain Controller. Added a Virtual Internal Switch in Hyper-V. After installing Windows, I started to add and configure the AD role.

Created the domain, and because I think I know what I’m doing didn’t check anything else.

Installed my second Windows 2012 server that needs to become a SQL 2012 Server. Then I tried to add it to the domain what resulted in an error: Could not find a domain controller for the domain…. Ping to the domain controller is working so why is this not working?

Let’s have a look at the DC, running DCDIAG gives me the following error:
The host 178cda1d-ee16-4f2d-8256-26f714b0770d._msdcs.personal.local could not be resolved to an IP address. Check the DNS server, DHCP, server name, etc
Check that the IP address is registered correctly with the DNS server.

Executed a demotion for the domain, and a DCPROMO to recreate it, same result, found some blogs about ipconfig /registerdns, net stop netlogon, net start netloging, dcdiag /fix etc. Nothing seemed to help and then it did hit me in the face…….

I take a look at the NIC on the virtual and found I did not configure a static IP yet, so I’t had a APIPA (169.254.0.x) range address. And DNS does not work with APIPA addresses!!!! Stupid mistake.

Changed to a 10.0.0.x range, restarted DNS and executed DCDIAG, no errors anymore!

Never start working on something when it’s time to take a nap

Chrome & IE “page cannot be displayed” error

 

Chrome additional info: Error 137 (net::ERR_NAME_RESOLUTION_FAILED): Unknown error.

You might ever have seen this message’s, okay you could have seen them in English but I’m current on a dutch system.

 

What is happening in my case? I’m using Windows 8 with Hyper-V installed. Having a Wireless Internal NIC and created 2 virtual LAN’s in Hyper-V ExternalNetwork & InternalNetwork.

The ExternalNetwork shares the local WIFI NIC with the Hyper-V Virtuals and has Internet Access. The Internal(lokaal)Network is only sharing local network trafic between my notebook and Hyper-V virtuals.

At my current WIFI network I received a 10.X.X.X IP and gateway address to join the Internet. My InternalNetwork is also using a 10.X.X.X IP and a gateway address also with 10.X.X.X gateway difference from the ExternalNetwork Gateway.

When starting IE or Chrome and browsing the Internet the browser is trying to get there true one of the given networks. It seems to be choosing the InternalNetwork instead of the ExternalNetwork and because the gateway & DNS IP points to my Virtual DNS server it will not be able to resolve anything.

Solutions on the Internet say to use:

• route print and then delete the incorrect route with route delete (open a administrative command prompt and give it a try)

It didn’t work for me but I could be doing something wrong.

My solution:

1. Start Control Panel
2. Network and Internet
3. Network Center
4. Change Adapter Settings
5. Select Properties for the InternalNetwork
6. Select Properties for the TCP/IP Protocol, I have only IPv4 enabled
7. Go to the Advanced section
8. Remove the current gateway and add a new one with a Metric set below 30
   

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.

Dynamic Intelligence | LinkedIn | Blog | Twitter

Could not access the Search administration database, SharePoint 2010

Could not access the Search administration database. A generic error occurred while trying to access the database to obtain the schema version info.

I encounterd this error before but forgot to blog about it, when I search the internet I do find several solutions.
http://www.google.co.uk/url?sa=t&rct=j&q=could%20not%20access%20the%20search%20administration%20database&source=web&cd=1&ved=0CFoQFjAA&url=http%3A%2F%2Fsureshsundar21.blogspot.com%2F2010%2F07%2Fcould-not-access-search-administration.html&ei=-7_oT-C4KcrW8QO_26CkCg&usg=AFQjCNEp5r6GolVYkb3RqsNQoA-GSWA75w

http://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/thread/71f0de97-6245-4dfd-80d0-75a55d3bc498

But what is causing this? In my case it's that I'm using a Named Instance with Dynamic IP-Port settings. The simple way to get rid of this error is by changing the Dynamic Port to the default port 1433 for all available IP entries in SQL Server Configuration Manager.

1. Click Protocols for <INSTANCE>
2. Right click TCP/IP and select Properties
3. Remove the 0 on the TCP Dynamic Ports
4. Enter the 1433 port number on the TCP Port
5. Click OK and restart the SQL Server (<INSTANCE>) Service

Add PC01 to the domain and allow remote desktop access by Group Policy

In my demo environment I like to use PC01 as a demo client PC.

First step is adding the PC01 to the demo.local domain. Of course the IP-Configuration must be set to make sure the PC01 can find the domain controller.

Adding the PC01 to the domain can be done by the same steps as in this blog about SQL01. [Blog]

After the PC01 is added to the domain I would like to be able to use Remote Desktop to connect to the PC01. However when I try to I will get the error:

It’s now a domain computer so I will not add users to the local group or change the local policy on the PC. Let’s use a Domain Policy to do this.

Login to you Domain Controller and click start and then type gpmc.msc and hit enter. The Group Policy Management console will start when you did it correct.

As you can see I created a Workstations OU in my Active Directory, I also moved the PC01 to this OU. All following steps will be based on this OU.

Right click the Workstations OU and select Create a GPO in the domain…….

Give the policy a name and click OK

Because we created the policy when we had the Workstations OU selected a link is created on the Workstations OU and the policy is created in the Group Policy Objects.

Right click the Policy and select Edit, the following screen will appear

Browse to the node as seen in the image and double click the Allow users to connect remotely using ………

Select enabled and click OK

Now the RDP access is enabled but we still not logon with RDP clients to the PC01. First we need to make sure that the users that you want to be able to do this are member of the local Remote Desktop Users group on the client.

This we will also do within the same GPO.

Still with the just created GPO open go to the Restricted Groups node as shown in the Image and click Add Group.

Click browse in the next windows and when the below window appears type remote, click Check Names and select the Remote Desktop Users group. Click OK.

And Click OK Again..

Click Add as shown below and then Browse to select the users/groups who you want to grant these rights..

In my case I created a group in AD that contains the users/groups that I want to be able to use the RDP rights to workstations. After selecting your users/groups click OK

And OK again

Close the GPO editor window. Go to the GPMC and show the settings of the just created GPO.

Seems to be okay, so are we finished? When trying to login true RDP with a user that is member of the GRANT_RemoteDesktopToWorkstations it still does not work..

Did you refresh your PC01? To make sure that the GPO’s are enforced? No?

Access the PC01 by the Virtual Management Console in my case Hyper-V and login with a account with administrative permissions….

Click the Windows start button, type CMD, right click CMD and select Run as Administrator

 

Type GPUPDATE /FORCE and hit enter, or restart the PC01..

Succeeded!!!!

 

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.
Dynamic Intelligence | LinkedIn | Blog | Twitter

Adding SQL01 to the demo.local domain & Installing SQL 2008R2

In my previous blog I did setup my Domain Controller for the demo.local domain. This blog is about adding SQL01 as a SQL 2008R2 server to the domain. I will not explain how to Install SQL 2008R2, this is done before. Download the PDF here: Blog2 - SQL Server 2008R2 Installation (PDF Download)

This blog is not only for those who already know everything, but also for those who are only using SQL server and never installed there own servers or added them to a domain. So how simply this might be I will explain it below.

I hope you understand how to connect to your server as an administrator after you have installed Windows 2008.

Before we can install SQL 2008R2 on the server we need to add the server to the domain. The first thing to do is to set the IP-Configuration:

Next step is to get to the location where you can add the server to the domain.

Click the Windows Start button, then right click Computer and select properties.

Click Change Settings in Computer name , Domain and workgroup settings

On the System Properties screen , type a computer description if you like and then click Change

On the Computer Name / Domain Changes screen select member of Domain and type your chosen domain name. In my case demo.local and click OK.

When everything is correct, the server will find the domain controller and will ask for credentials that have the right to join the server SQL01 to the domain. In my case the demo.local\administrator.

After clicking OK it can take a while depending on your environment. But when correct the next screen will appear, click OK.

After clicking OK a warning will appear that you need to restart the server. Click OK.

Click Close on the System Properties Screen, now the restart screen appears, click Restart Now.

After the SQL01 Server is restarted start a remote desktop connection to the server again. Login with a domain demo.local\administrator account to the server to see or adding the server to the domain succeeded.

On the Server Manager page we can see the server joined the domain

We can check on some more places. Login to your domain controller and start Active Directory Users & Computers. Select the Computer node and notice that your server is there.

Start the DNS Manager on the domain controller, open the demo.local node and notice that your server is there.

So we now added the server to the domain, you can now repeat this for all other servers that you like add to the domain.

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.
Dynamic Intelligence | LinkedIn | Blog | Twitter

Configuring the Domain Controller

This blog describes the steps I made to install my Domain Controller. The starting point is Windows 2008 R2, with all available updates until 20th of May 2012 and Remote Desktop Access is enabled.
First step to make is configuring the TCP/IP settings to match my current network configuration.

The preferred DNS is using the local host addressing to make sure the DC01 will be able to find is own DNS environment. The Alternate DNS point to my Router.
Start the Server Manager tooling and click Add Role

On the Before You Begin screen click Next

On the Select Server Roles screen mark the check box “Active Directory Domain Services”

A message box appears to add the required features that makes it possible to install the Active Directory Domain Services. Click Add Required Features.

Click Next to continue installation of the Active Directory Domain Services.

On the next screen features of the Active Directory Domain Services are explained including the components that will be installed for making DC01 a fully runnable Domain Controller. Click Next.

On the Confirm Installation screen read if you want and then click Install

Installation is running, have some patients to let it finish.

In my case the Installation Results windows shows a Warning about not having enabled automatic update. I like to know what is happening when updating this server and keep it this way. It’s your own choice, click close to continue.

On the server manager screen we can see that the Active Directory Domain Services is installed but shows a red cross. This is because we need some other steps to complete.

Click the Windows Start button and type dcpromo in the search box. When in the Programs list dcpromo appears right click it and select “Run as Administrator”.

A small screen appears that checks all is installed to get on with dcpromo.

On the Welcome screen click Next

My demo & test environment contains only Windows 2008 or higher server editions. This message warns for using “older” security based servers. Read if you like and then click Next.

This will be the first domain controller for a new forest, so select create a new domain in a new forest and click next.

Now we need to name the forest root domain, you can choose what you like, mine is going to be demo.local and click Next

A window appears that’s going to check or the given root domain is available.

Now we need to choose the forest functional level, if you plan to add multiple domain controllers to the forest based on different Windows Server editions you need to make the choice you need. In my environment I will not add Server edition lower then Windows 2008 Server, so that is selected and then click Next.

Same for the domain functional level

The install will now check or DNS is configured…

This is the first DC in the domain so all settings are as desired, click Next

A warning appears that A delegation for this DNS server connect be created …… this is correct, it’s the first DC/DNS in the domain and I’m not integrating with an other domain. Click Yes to continue.

I leave all locations as default and click Next

Type your Restore mode password and click Next. Remember it if you ever need to access the restore mode…

On the Summary screen you can read the selections you made and then click next to start configuration.
When this is a try and you would not like to do this again you can Export your settings to a Answer file to use for unattended installation.

The installation will now start, have patients to let if finish….

Click Finish to complete the installation…

The restart screen appears, I select DO NOT RESTART…. Because I want to clear all windows logs before restarting…

Click Windows Start button, type eventvwr in the search box and hit enter.
The Event Viewer screen will appear, open the Windows Logs node, right click the Application node and select clear log, click clear in the appearing windows.

Repeat above step for security, setup and system log.
Now restart your server! It might take a while before it’s up and running again……
The first thing that you might notice is that you can’t login based on your local credentials.

This is now a Domain Controller so you need to login with a domain account, you local account is converted automaticly so you only need to choose your domain. Click Use another account.. type <domain>\<user> to login.

After you logged in check the Windows Logs with the Event Viewer to see or there are a lot of issues. In my case only the time services needs some attention, this is the primary domain server so he can’t find a top level time services above him. I will fix this later.
Also an error eventId14550 is logged. See http://technet.microsoft.com/en-us/library/ee411032(v=ws.10).aspx for more information…
The simple thing is during startup this services can’t register, following the instructions on the given URL should fix this.

And yes, it’s now starting successful

We are finished for now, in administrative tools the DNS / DC tools are available, start playing…

All is there?
The root domain:

We can manage Active Directory:

And also DNS seems to be up and running…

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.
Dynamic Intelligence | LinkedIn | Blog | Twitter